Search code examples
google-sheetsgoogle-sheets-query

Google Sheets Query by Max Timestamp

I am trying to query a list of timestamps, unique IDs, and data that looks like this:

A - Unique ID

B,C,D,E - Data, but only E is important

F - Timestamp

I want to return A and E where F is the most recent timestamp.

Currently I am using the following query but it returns multiple IDs and timestamps instead of just the MAX timestamps:

=QUERY(A:F,"SELECT MAX(F),A,E WHERE E != '' GROUP BY A,E")

Any ideas?

Solution

  • See if this works

    =ArrayFormula(iferror(vlookup(unique(Sheet1!A2:A), sort({Sheet1!A2:A, Sheet1!E2:E, Sheet1!F2:F }, 3, 0), {1, 2, 3}, 0)))

    EXPLANATION:

    1. Sort() is sorting the ranges descending, based on column F
    2. Unique() gets the unique ID's
    3. Vlookup() looks for those unique ID's and returns the first three columns {1, 2, 3} of the ranges {Sheet1!A2:A, Sheet1!E2:E, Sheet1!F2:F } (so the values in A, E and F). Because Vlookup() only returns the first match, the data with the most recent timestamp will be returned.