Finding Latest Dates in Range

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

Using the follwoing sample:

A B C
1. C1 2/4/04
2. C1 5/6/04
3. B1 4/5/04
4. B1 7/7/04
..
..
..

Is there a way to populate column C with the latest date
for each "group" in column A.

For example, C1 would be blank, C2 would have 5/6/04, C3
would be blank, and C4 would have 7/7/04.

If there is a solution, I assume that the workbook would
have to be sorted on column A and then column B?

TIA.
 
Using the follwoing sample:

A B C
1. C1 2/4/04
2. C1 5/6/04
3. B1 4/5/04
4. B1 7/7/04
.
.
.

Is there a way to populate column C with the latest date
for each "group" in column A.

For example, C1 would be blank, C2 would have 5/6/04, C3
would be blank, and C4 would have 7/7/04.

If there is a solution, I assume that the workbook would
have to be sorted on column A and then column B?

TIA.
Click to expand...

You could use this *array* formula in C1, and copy/drag down as needed:

=IF(A1="","",IF(B1=MAX((A1=$A$1:$A$100)*$B$1:$B$100),B1,""))

To enter an array formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.

Adjust the Row 100 reference as needed.


--ron
 
If the data is sorted on the date column as your sample seems to suggest:

=LOOKUP(2,1/(($A$2:$A$5=E2)*$B$2:$B$5),$B$2:$B$5)

where E2 houses a group value like C1, E3 B1, etc.
 
Back
Top