1

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.

1

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))
1
  • Thanks @JPV +1 for demonstrating on the spreadsheet for me - most appreciated ;-) Apr 13 '15 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.