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))
Best Answer
Check out this formula I entered in G2 of the sheet 'SHEETJP' in the spreadsheet you shared: