0

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.

1

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.

  • Thank you so much for this - not only is this answer accurate, it was also exactly what I needed and very well explained. That is a very creative trick to use the VLOOKUP to intentionally find an erroring result – user4815162342 Nov 16 '18 at 3:41
  • 1
    When it comes down it, most of what any of us do is "tricks." I still can't believe the whole digital world is running on combinations of zero and one. Seems like magic to me! The only difference is that, here, the magicians share their secrets. – Erik Tyler Nov 17 '18 at 4:27

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.