Return Max Date

K

kpotg

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.
 
T

T. Valko

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,MATCH(541,A2:A6,0)+COUNTIF(A2:A6,541)))

Format as Date
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top