Return Max Date
 

I have a table with codes and dates not in consecutive columns. The codes
may repeat and each code could have a different date. I only need the max
date for each code type. The table has about 3200 rows and could change
size. It is something similar to the example below:

541 John Smith 11/01/08
541 John Smith 12/01/09
231 Allison Miller 10/01/08
340 Mary Johnson 09/09/09
000 Steve Rogers 11/10/09

If I was searching for 541, I would need 12/01/09 to be returned. Please
let me know what formula would be best to use in this case.

Return Max Date
 

Try one of these...

Array entered** :

=MAX(IF(A2:A6=541,D2:D6))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Format as Date

The table has about 3200 rows

If you have 1000's of rows of data and there are duplicate codes *and* the
data is sorted so that all duplicate codes are grouped together this longer
formula will be more efficient.

Normally entered:

=MAX(INDEX(D2:D6,MATCH(541,A2:A6,0)):INDEX(D2:D6,M ATCH(541,A2:A6,0)+COUNTIF(A2:A6,541)))

Format as Date

--
Biff
Microsoft Excel MVP


"kpotg" wrote in message
...
I have a table with codes and dates not in consecutive columns. The codes
may repeat and each code could have a different date. I only need the max
date for each code type. The table has about 3200 rows and could change
size. It is something similar to the example below:

541 John Smith 11/01/08
541 John Smith 12/01/09
231 Allison Miller 10/01/08
340 Mary Johnson 09/09/09
000 Steve Rogers 11/10/09

If I was searching for 541, I would need 12/01/09 to be returned. Please
let me know what formula would be best to use in this case.