3

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?

1 Answer 1

5

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.
5
  • 1
    I'm glad you are around JPV !! Mar 24, 2015 at 9:45
  • Thanks, that works! I'm still trying to decipher it (I was hoping for something much simpler, along the lines of a LAST function or similar...clearly doomed to fail).
    – Chris
    Mar 24, 2015 at 15:53
  • 1
    @Chris: just added a brief explanation to help you 'decipher'.
    – JPV
    Mar 24, 2015 at 19:29
  • @JacobJanTuinstra: you are too kind. :-))
    – JPV
    Mar 24, 2015 at 19:32
  • Thanks for the explanation...it makes perfect sense now!
    – Chris
    Mar 24, 2015 at 19:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.