Retrieve Most Recent Data In Column

  • Hi Guys,

    I'm trying to retrieve the most recent data from a column for a particular item. I've attached the spreadsheet to make it easier to explain.

    The Latest Ratings sheet has 2 Funds, the rating of that Fund, and the date of the rating.

    The Rating History sheet is a history of all the ratings, which the user can add to over time.

    I need the the latest ratings data to be retrieved from the history.

    I'm hoping a formula will suffice, but can't think of one because it goes beyond a simple index match combo.

    Thanks so much.


  • Re: Retrieve Most Recent Data In Column


    Hummm Formula...
    =INDEX('Rating History'!$B$5:$D$8,MAX(IF('Rating History'!$B$5:$B$8=$B7,ROW('Rating History'!$B$5:$B$8)-4)),COLUMN(B1))
    confirm with Ctrl + Shift + Enter (Array forumla entry)
    then copy right + down.

  • Re: Retrieve Most Recent Data In Column

    Wow super quick and worked a treat - thanks jindon.

    The actual workbook will have the user making new entries and so I changed the index to $B:$B for the whole column. Will this make it more resource-intensive because of the array formula? Should I stick to a set range?

  • Re: Retrieve Most Recent Data In Column

    I would not use formula....

    You can either use vba or do it manually...
    1) select data range and copy including header.
    2) paste to the other sheet and sort descending by date.
    3) go to [RemoveDuplicates] by first column.
    4) sort ascending by first column.

    That's what the code does.

  • Re: Retrieve Most Recent Data In Column

    OK thanks. Can I ask why you wouldn't use the formula?

    I'm hesitant to use VBA because the user may forget to hit the update button, or keep unnecessarily hitting it each time.

  • Re: Retrieve Most Recent Data In Column

    The attached always updates automatically whenever you select "Latest Ratings" sheet.
    In ThisWorkbook module

    Private Sub Workbook_Open()
        Run Sheets("latest ratings").CodeName & ".worksheet_activate"
    End Sub

    In "Latest Ratings" sheet module

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!