Matching max in a subgroup with corresponding data pair
 

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

Matching max in a subgroup with corresponding data pair
 

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


" wrote:

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

Matching max in a subgroup with corresponding data pair
 

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