Query rows with most recent time stamp in Google Sheets

1
votes

I have some data in Google Sheets that contains duplicate account numbers with different dates. I want to query the data and to remove the duplicates, and select only those rows with the most recent date.

Sample data is here:

115684  11/21/2019  Blue
115684  11/20/2019  Green
115684  11/19/2019  Yellow
165488  11/17/2019  Red
165488  11/12/2019  Blue
165488  6/19/2019   Green
556689  11/21/2019  Yellow
556690  11/21/2019  Red
556691  6/19/2019   Blue

so the result should look like this:

115684  11/21/2019  Blue
165488  11/17/2019  Red
556689  11/21/2019  Yellow
556690  11/21/2019  Red
556691  6/19/2019   Blue

I tried this, but it didn't work:

=QUERY(A:C, "SELECT A, B, C WHERE MAX(B)")
1
google-sheetsduplicatesuniquegoogle-sheets-formulagoogle-sheets-query
I ended up using this: – Carlo B.
=SORTN(SORT(A:C,2,0), 999999999, 2, 1, 0) which is the same as player0 suggested with a slight modification to the column selection – Carlo B.

1 Answers

1
votes

try:

=SORTN(SORT(A1:C, 2, 0), 99^99, 2, 2, 0)

0