I wanted to use this query =query(G2:I80,"select G,H,I where H=max(H) group by I",-1)
From this:
Cdd Cts T
NWH 4 A1
LBB 3 A1
MP 3 A1
DC 2 A1
AK 10 A10
CC 3 A10
SC 2 A11
JL 1 A11
VT 1 A11
To This:
T Cdd Cts
A1 NWH 4
A10 AK 10
A11 SC 2
Am I doing it right? Or I have to come up with something to manually count them?
Try
=ArrayFormula(iferror(vlookup(unique(I2:I), sort({I2:I, G2:H},3,), {1,2,3},0)))
and see if that works for you ?
How does it work:
1: with sort() a new 'virtual' table is created with col I as the first column. That new table is sorted on col H (latest dates appear on top). 2: a list with the unique values is retrieved from col I (with the unique function) 3: that list is 'looked up' (with vlookup). When a match is found it retrieves the first 3 columns ({1, 2, 3} or: col I, G and H). Vlookup only returns the first match found: since our list is sorted, that will be the entry with the latest date. 4: if nothing is found an error is suppressed with the iferror().