Google Sheets Query for Maximum Date

1
votes

I have a table which I would like to return in its entirety but filtered by column H with the maximum date as rows have duplicate account numbers with different dates.

https://docs.google.com/spreadsheets/d/1x3hYy1igiL3_lqhFE3IwrQNFbOjdpXRtHLAbOXB2BE4/edit?usp=sharing

I'm using this query right now, but clearly I'm overlooking something, can anyone help, please?

=QUERY(B3:I12, "Select *, Max(H) WHERE B is not null Group by C")
2
arrayssortinggoogle-sheetsuniquegoogle-sheets-query

2 Answers

1
votes

correct syntax would be: 

=QUERY(B3:I12, 
 "select B,C,D,E,F,G,H,I,max(H)
  where B is not null
  group by B,C,D,E,F,G,H,I")

but you probably need this:

={B3:I3; SORTN(SORT(B4:I, 7, 0), 99^99, 2, 2, 1)}
0
votes

The final formula was ={DataTable;SORTN(SORT(Data,17,0),99^99,2,2,1)}

DataTable is the headings and Data is the dataset. Out of 14,000 lines this filtered down to around 7,000 lines of unique rows with the highest (Maximum) date.