Oldest date for Duplicate Cust. #

[MathewS]

I'm trying to get the oldest date associated with a
customer number, and in the Cust# column, i'll have many
duplications of the same customer number.
Let's say A is "Date", and B is "Cust#".
(I won't be able to allow my users to sort the data, so
i'll need a formula that returns either the oldest date,
or the cell which contains the oldest date.)

Any help is much appreciated!

 

[MathewS]

Nevermind. I found it using Google/Groups.
{=MIN(IF($B$1:$B$10=B1,$A$1:$A$10))}

 

[Andre Croteau]

Hi Matthew,

Suppose you have data in cells A8:B24, Whereby column A has "Customer #"
and column B has "dates"
and you are inputing a Customer # in cell B1:

In Cell C1 you can use this array formula to give the oldest date:

=MAX(($A$8:$A$24=$B$1)*($B$8:$B$24)) entered with Shift-Control ENTER


In cell D1 , you can use this array formula to give the LOCATION (row
number) of the oldest date:

=MAX(ROW(8:24)*(IF(($B$8:$B$24*($A$8:$A$24=$B$1))=MAX(($A$8:$A$24=$B$1)*($B$
8:$B$24)),1,0))) entered with Shift-Control ENTER


Hope this helps

André