Google Sheets – Select Highest Value for Each Unique Label

google sheetsgoogle-sheets-query

I have a simple spreadsheet which is tracking timeseries data for several users:

Time | User | info
00:01| bob  | hello
00:03| sue  | hello
00:05| bob  | goodbye
00:10| sue  | hello again

I would like to isolate the most recent entry for each unique user in another sheet. Also, I'd like this view to be dynamically updating, as more rows are added to the source data sheet.

Time | User | info
00:05| bob  | goodbye
00:10| sue  | hello again

This question is very similar: Google Spreadsheet Query for unique and "most recent", however it gives me an error related to the <> syntax, which I cannot figure out.

Best Answer

There are a lot of approaches you could take to this. Here's one (written as if your data is held in A:C on "Sheet1":

=ArrayFormula(QUERY(IFERROR(VLOOKUP(UNIQUE(Sheet1!B:B),QUERY({Sheet1!A:C},"Select Col2, Col3, Col1 Order By Col1 Desc"),{1, 2, 3},FALSE)),"Select Col3, Col1, Col2"))

Be sure to format your time column in the result range in accordance with the time column in your raw data range.

How it works (from the inside out):

  1. The innermost QUERY takes the raw data and lists it in reverse order by time, leaving the most recent time at the top. It also swaps the order of the columns so that the name column is first: QUERY({Sheet1!A:C},"Select Col2, Col3, Col1 Order By Col1 Desc")

  2. The VLOOKUP searches only the UNIQUE entries among the names (limited each to one find), by looking in the QUERY range we just created in memory above. It returns all three columns of data (i.e., {1, 2, 3}). We use "FALSE" since the data is in no particular order by name.

  3. UNIQUE includes one null value if contained in the range, and VLOOKUP will throw an error for a null search. So we wrap everything so far in IFERROR() to cut that one error.

  4. We run another QUERY on our VLOOKUP results to put the columns back in the original order: QUERY(...,"Select Col3, Col1, Col2")

  5. Finally, everything is wrapped in "ArrayFormula," because we are retrieving results for an entire range, not just one cell.

Related Topic