Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rob Rob is offline
external usenet poster
 
Posts: 234
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,726
Default 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




  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rob Rob is offline
external usenet poster
 
Posts: 234
Default 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






  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Vix Vix is offline
external usenet poster
 
Posts: 9
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Time and Date stamp - Pivot Chart Bemidji Excel Discussion (Misc queries) 6 October 25th 06 02:50 PM
return a number from a cell based on the lastest date sarahmarsden Excel Worksheet Functions 2 October 22nd 06 11:09 PM
finding earliest date within a range by employee Steve Excel Worksheet Functions 4 October 19th 06 11:34 PM
NETWORKDAYS - Multiple Date Selection Annabelle Excel Discussion (Misc queries) 3 October 4th 05 07:04 PM
need help finding a Date range within long list A shink Excel Worksheet Functions 2 March 30th 05 05:01 PM


All times are GMT +1. The time now is 08:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"