Search code examples
arrayssortinggoogle-sheetsuniquevlookup

Google Sheets - Query or Filter data by ID and most recent date

I'm having trouble filtering a set of data containing Note history. Several notes may be left for the same ID, but I want to pull only the most recent notes left, for each unique ID.

Here is a sample dataset. There are several columns - I want to pull columns A,B,C,E,F,G,H,L,M,N,O,P. The ID is in Column A and the date is in Column M.

I also want to exclude any rows with blank Notes (Column L).

I have a formula, below, based on research into how to pull what I want, but it's pulling the earliest dates rather than the most recent dates and I haven't figured out how to do the opposite - and pull the most recent dates as I need to do. I also tried to use a QUERY looking at Max(M) group by A, but I got an error.

=ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE('Data - All'!A2:A),SORT({'Data - All'!A2:C,'Data - All'!E2:H,'Data - All'!L2:L,'Data - All'!M2:M,'Data - All'!N2:P},4,0),{1,2,3,4,5,6,7,8,9,10,11,12,13,14},0)))

My formula also doesn't pull the header and doesn't exclude rows where the Note column is blank.

Any help is greatly appreciated.

Thanks!

Solution

  • try:

    =ARRAYFORMULA({'Data - All'!A1:P1;
 SORTN(SORT('Data - All'!A2:P, 13, 0), 9^9, 2, 1, 1)})

    13  - column with dates
0   - descending (latest first)
9^9 - return all rows
2   - sortn grouping mode
1   - group by first column
1   - in ascending order

    enter image description here