Retrieve Most Recent Data In Column

davede · Oct 11th 2015

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.

Dave

forum.ozgrid.com/index.php?attachment/67169/

jindon · Oct 11th 2015

Re: Retrieve Most Recent Data In Column

Try

Code

Sub test()
    With Sheets("latest ratings")
        .[b6].CurrentRegion.Clear
        Sheets("rating history").[b4].CurrentRegion.Copy .[b6]
        With .[b6].CurrentRegion
            .Sort .Cells(1, 3), 2, , , , , , 1
            .RemoveDuplicates 1, 1
            .Sort .Cells(1, 1), 1, , , , , , 1
        End With
    End With
End Sub

Display More

Hummm Formula...
C7
=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.

davede · Oct 11th 2015

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?

jindon · Oct 11th 2015

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.

davede · Oct 11th 2015

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.

jindon · Oct 11th 2015

Re: Retrieve Most Recent Data In Column

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

Code

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

In "Latest Ratings" sheet module

Code

Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    Me.[b6].CurrentRegion.Clear
    Sheets("rating history").[b4].CurrentRegion.Copy Me.[b6]
    With Me.[b6].CurrentRegion
        .Sort .Cells(1, 3), 2, , , , , , 1
        .RemoveDuplicates 1, 1
        .Sort .Cells(1, 1), 1, , , , , , 1
    End With
    Application.ScreenUpdating = True
End Sub

Display More

davede · Oct 11th 2015

Re: Retrieve Most Recent Data In Column

ok nice one - thanks for again for all your help.

Retrieve Most Recent Data In Column

Participate now!

Share