Array or Lookup?

[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#".
Any help is much appreciated!

 

[Mathew P Bennett]

Hi Matt
I would use a combination of Advanced Filter then Array enter a Min formula,

A B C D
Date Customer Customer Date

Columns A & B are your original data.
Column C will be the Unique entries (no duplicates) from your original data
A. Highlight Column B, Data,Filter,Advanced Filter. Choose Copy to another
location (C1 in this case), & choose Unique values only.
Now in Column D type this formula
{=MIN(IF($B$2:$B$1000<>$C2,FALSE,$A$2:$A$1000)) } Remeber Arraty
Entered

Good luck I hope this helps

Mathew

 

[Mathew P Bennett]

Hi again Matt

For oldest date use MAX !!

Mathew

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#".
Any help is much appreciated!

 

[Mathew P Bennett]

Sorry. cracking up. Of course oldest = MIN

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#".
Any help is much appreciated!

 

[Dave Smith]

If you can sort your table by date, here's one way:

If the left table should give the result on the right

A B C D E
Cust # Date Cust # Oldest Date
1 1001 9/1/2003 1001 9/1/2003
2 1002 9/2/2003 1002 9/2/2003
3 1003 9/3/2003 1003 9/3/2003
4 1004 9/4/2003 1004 9/4/2003
5 1001 9/5/2003
6 1004 9/6/2003
7 1002 9/7/2003


then put this formula in E2 and copy down

=VLOOKUP(D2,A2:B8,2,FALSE)

HTH

-Dave

 

[MathewS]

Actually I cannot sort or filter this.
-Mathew

-----Original Message-----
Hi Matt
I would use a combination of Advanced Filter then Array enter a Min formula,

A B C D
Date Customer Customer Date

Columns A & B are your original data.
Column C will be the Unique entries (no duplicates) from your original data
A. Highlight Column B, Data,Filter,Advanced Filter. Choose Copy to another
location (C1 in this case), & choose Unique values only.
Now in Column D type this formula
{=MIN(IF

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, if your
range in Columns A and B is named "datarange", and if you enter the
customer number in Cell C1

=MIN(--columnvector(arrayrowfilter1(datarange,2,C1),1))

Alan Beban