Google Sheets – Query for Unique and Most Recent Entries

google sheets

Given a Google spreadsheet like this:

spreadsheet list

I would like to create a query that shows only the most recent row for each unique name, so the results would look like this:

spreadsheet query results

What would that query look like?

Best Answer

Maybe give this a try:

=ArrayFormula(sort(vlookup(query({row(A:A),sort(A:C)},"select max(Col1) where Col4 <> '' group by Col4 label max(Col1)''",0),{row(A:A),sort(A:C)},{2,3,4},0),1,0))

Explanation

  • {row(A:A),sort(A:C)}

will create a new (virtual array), consisting of 4 columns: 1 column with the row numbers + 3 columns of the original table (but sorted, so that the 'latest' dates will be at the bottom of the table)

  • query({row(A:A),sort(A:C)},"select max(Col1) where Col4 <> '' group by Col4 label max(Col1)''",0)

The query selects the max row number (col1) (per name (group by Col4)).

  • these row numbers (returned by the query) are 'looked' up in the same 'virtual' array (see above) and on match, columns 2, 3 and 4 are returned.
Related Topic