Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return date if in range, else return blank | Excel Worksheet Functions | |||
return a date from range, date is between dates in two other cells | Excel Discussion (Misc queries) | |||
Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) | |||
calc constant date from variable date & return with ability to rn. | Excel Worksheet Functions |