Matching max in a subgroup with corresponding data pair

[jconta]

I'm trying to write a function which returns the quantity purchased
from the most recent order given a list of customers, order dates, and
quantities.

Input would look like this:
Customer # Order date Quantity
1 3/5/06 2
1 4/12/06 5
1 5/19/06 4
2 1/3/06 3
2 4/5/06 4
3 2/12/06 2
3 3/19/06 1
3 5/22/06 7

Results would look like:

Customer # Most Recent Order Quantity
1 5/19/06 4
2 4/5/06 4
3 5/22/06 7


I've been trying to use INDEX(MATCH) but have not been successful,
probably because its the Friday of a long weekend.

Thanks again for everyone's help - Happy 4th.

JC

 

[Guest]

If orders aere sorted by Ascending customer number and DESCENDING date then:

To get date:

=INDEX($B$2:$B$9,MATCH(A11,$A$2:$A$9,0))

to get qty:

=INDEX($C$2:$C$9,MATCH(A11,$A$2:$A$9,0))

If BOTH are sorted ASCENDING then:

=INDEX($B$2:$B$9,MATCH(A11,$A$2:$A$9,0)+COUNTIF($A$2:$A$9,A11)-1)

and (enter as array formula with Ctrl+ShiFt+Enter)

=INDEX($C$2:$C$9,MATCH(1,($A$2:$A$9=A11)*($B$2:$B$9=B11),0))

Sample below:

Customer Date Quantity
1 05/03/2006 2
1 12/04/2006 5
1 10/05/2006 4
2 01/03/2006 3
2 04/05/2006 4
3 12/02/2006 2
3 19/03/2006 1
3 22/05/2006 7

1 10/05/2006 4 <==row 11
2 04/05/2006 4
3 22/05/2006 7

HTH

 

[Herbert Seidenberg]

Arrange your data like this:
CustA DateA QtyA CustB DateB QtyB
1 3/5/06 2 1 5/19/06 4
1 4/12/06 5 2 4/5/06 4
1 5/19/06 4 3 5/22/06 7
2 1/3/06 3
2 4/5/06 4
3 2/12/06 2
3 3/19/06 1
3 5/22/06 7

Name the six columns with the headers suggested.
Use Insert > Name > Create > Top Row
Create the CustB list with
Advanced Filter > Unique records
Check Tools > Options > General > R1C1 Ref Style
Create the DateB and QtyB list respectively with
=SUMPRODUCT(MAX((CustA=CustB R)*DateA))
=SUMPRODUCT((CustA=CustB R)*(DateA=DateB R)*QtyA)
Uncheck R1C1