Finding Lastest Date

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

Hi,

Using Exel 2000

I have thousands of records which have duplicate account numbers but the
unique entry is an end date of an agreement. What I'd like to have is a
column adjacent to the end date with a formula that finds the maximum date
for each of the many accounts for example, the first and last records would
have the end date placed in the adjacent column as they are the most recent
agreements.

AccNo AccName Start End

A0740 MR M CLAYDON 11/01/2006 11/01/2011

A0070 MR M WARREN 04/09/2002 04/09/2005

A0070 MR M WARREN 05/09/2005 05/09/2008



Not always easy to explain, but hopefully some help is available.



Thanks, Rob
 
=IF(D2=MAX(IF($A$2:$A$2000=A2,$D$2:$D$2000)),D2,"")

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
Thanks Bob

Bob Phillips said:
=IF(D2=MAX(IF($A$2:$A$2000=A2,$D$2:$D$2000)),D2,"")

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
Click to expand...
 
Back
Top