Google Sheets – How to Filter Rows by Unique Column of Sorted Values

google sheets

I need an equation that filters by unique ID, using only the latest row (most recent row in time), and data found in that row.

I have data that is in the following format:

ID | Price | Area | Status | Date
10 | 20000 | 75   | Buy    | 20150301
11 | 30000 | 100  | Buy    | 20150201
10 | 23000 | 75   | Buy    | 20150101
23 |  4000 | 89   | Sold   | 20150601
11 | 34000 | 567  | Sold   | 20150120

After applying the filter equation I'd expect to see:

ID | Price | Area | Status | Date
23 |  4000 | 89   | Sold   | 20150601
10 | 20000 | 75   | Buy    | 20150301
11 | 34000 | 567  | Sold   | 20150120

Attempt 1 (DID NOT WORK AT ALL)

=FILTER(A2:E,A2:A<>OFFSET(A2:A,-1,))

From this suggestion: Similar problem from back in 2012 but I could not get this to work for me.

Attempt 2 (MISSING ID 23 & WRONG DATES)

=FILTER(A2:E6, A2:A6 = UNIQUE(A2:A6))

Attempt 3 (MISSING ID 11, RIGHT DATES)

=FILTER(SORT(A2:E6, E2:E6, FALSE), A2:A6 = UNIQUE(A2:A6))

Example spreadsheet with above workings.

Best Answer

Check out this formula I entered in G2 of the sheet 'SHEETJP' in the spreadsheet you shared:

=ArrayFormula(vlookup(query({row(A2:A),sort(A2:E)},"select max(Col1) where Col2 is not null group by Col2 label max(Col1)''",0),{row(A2:A),sort(A2:E)},{2,3,4,5,6},0))