Posts by gclabbe

    I have a list of data sorted like the following:

    DP 1.1
    MP -0.9
    MP -0.4
    MP -1.4
    DP 2.6
    MP -1.8
    MP 1.6
    DP 1.2
    DP -0.1
    MP -2.3

    I'd like to do some plotting that shows all the DP items in one color and all the MP items in another color.

    I cannot sort because this is time series data and other charts rely on the time nature of date in adjacent cells.

    Pivot tables are non-ideal (unless someone can explain dynamic pivot tables) since new data will be added daily and I don't want to rebuild the pivot every day.


    I was able to correctly rank the list by creating columns with:

    1) Rank by score (RS)
    2) Rank by days (RD)
    3) Aggregate = 10 * RS + RD
    4) Rank by Aggregate (AG)

    Remember to use the reverse rank bit when you create RD and AG.

    You could, of course, make 1 column that is steps 1, 2 & 3, hide this and then rank based on the hidden column.

    2nd idea would be to sort the data using the normal sort routine. This will reorder the data in rank form.


    I couldn't figure out how to do it with a cell function. There doesn't seem to be a search from the right function.

    Solution 1 would be to create a search or replace from the right function and use that.

    Solution 2 is a custom function, such as below.

    Hope this helps.

    Function InjectSpaces(strOLDTEXT As String)
    Dim strPIECES(3) As String
    intLength = Len(strOLDTEXT)

    intLAST5POS = InStrRev(strOLDTEXT, "5")
    intDASHPOS = InStr(strOLDTEXT, "-")
    strPIECES(1) = Left(strOLDTEXT, intDASHPOS - 1)
    strPIECES(2) = Mid(strOLDTEXT, intDASHPOS + 1, intLAST5POS - intDASHPOS)
    strPIECES(3) = Right(strOLDTEXT, intLength - intLAST5POS)

    strNEWSTRING = Join(strPIECES, " ")

    InjectSpaces = strNEWSTRING
    End Function

    Thank you very much for the time and effort you devoted to this problem!

    You managed to recover days 30 to 39 ... gives me at least a little history. I can fill in some gaps beyond this.

    What is the format of data in an Excel file? Is there a book or white paper that would help me understand the cell contents format in binary code?

    I tried to open the file in a hex editor and discern any data, but I couldn't manage to get a finger hold in the reverse-engineering wall climb.


    I've tried to use the Excel Rebuilder software. Didn't seem to work. I also tried to use text extraction from the Excel Rebuilder, but I can't figure out what this does (didn't give me data on a good file, either).

    The VBA macros that are in the file are non-functional (experimenting only) so I don't care if they are retained. The only thing I would like to have back is the 5+ weeks of historical data that I've got in the sheet (with no backup of course).

    If you manage to get it working, you will have intimate understanding of just how fat I am :coolwink:.



    My file was working as of Saturday. Opened it and added a couple of cells of data. This is a very simple spreadsheet with 5 or 6 columns of data and 4 charts. Somehow, though, when I try to open the file now, Excel looks like it's processing the file and then comes back blank. Even if I switch over to the VB Editor, there is nothing indicating a file is loaded.

    Any ideas how I can fix the file or get my data back?