Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time and Date stamp - Pivot Chart | Excel Discussion (Misc queries) | |||
return a number from a cell based on the lastest date | Excel Worksheet Functions | |||
finding earliest date within a range by employee | Excel Worksheet Functions | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
need help finding a Date range within long list | Excel Worksheet Functions |