Finding Lastest Date
 

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

Finding Lastest Date
 

=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)


"Rob" wrote in message
...
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

Finding Lastest Date
 

Thanks Bob

"Bob Phillips" wrote in message
...
=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)


"Rob" wrote in message
...
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

Finding Lastest Date
 

Try,

=IF(B2A2,B2,A2)

Assuming A2=Start Date and B2=End Date


"Rob" wrote:

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