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?

4

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.
  • 1
    I'm glad you are around JPV !! – Jacob Jan Tuinstra Mar 24 '15 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 '15 at 15:53
  • 1
    @Chris: just added a brief explanation to help you 'decipher'. – JPV Mar 24 '15 at 19:29
  • @JacobJanTuinstra: you are too kind. :-)) – JPV Mar 24 '15 at 19:32
  • Thanks for the explanation...it makes perfect sense now! – Chris Mar 24 '15 at 19:49

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.