Finding max value within a subgroup

[jconta]

Here's what I'm looking at:

I've got a data set which pairs a customer with the dates that customer
has ordered, looking like this:

Customer # Order date
1 3/5/06
1 4/12/06
1 5/19/06
2 1/3/06
2 4/5/06
3 2/12/06
3 3/19/06
3 5/22/06

And so forth. Its a very long list.
I need to write a function with the customer number as an input (the
subgroup) which finds the most recent order date (the max) and returns
that date, so the output would look like this:

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

Thanks in advance for your help,

JC

 

[Roger Govier]

Hi

With your data in A1:B9, set up a table with Customer numbers in say
D24.
In cell E2 enter the following Array formula. Do not type the curly
braces { } yourself, but commit the formula with Control+Shift+Enter
and Excel will insert the curly braces for you.

{=MAX(IF($A$2:$A$9=D2,$B$2:$B$9,0))}
Copy down through E3:E4

 

[Biff]

Hi!

Assume the raw data is in the range A2:B9

In G2 on down you have the unique customer #'s:

G1 = 1
G2 = 2
G3 = 3
etc

Enter this formula in H2:

=SUMPRODUCT(MAX((A$2:A$9=G2)*(B$2:B$9)))

Copy down as needed and format the cells as DATE.

Biff

 

[Biff]

I'm having one of those days..........argh!

In G2 on down you have the unique customer #'s:
G1 = 1
G2 = 2
G3 = 3
etc

Obviously, that should look like this:

G2 = 1
G3 = 2
G4 = 3
etc

Biff

 

[jconta]

Worked like a charm - thanks very much.

One other question - I have a third column next to the order date for
order quantity.
I'm trying to have it return the quantity purchased from the last order
I pulled using the function you gave.

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

 

[Roger Govier]

Hi

Try
=INDEX(A2:C9,MATCH(MAX(IF($A$2:$A$9=D2,$B$2:$B$9,0)),B2:B9,0),3)

 

[Roger Govier]

I should have said, of course, enter as an array formula with
Control+Shift+Enter to give

{=INDEX(A2:C9,MATCH(MAX(IF($A$2:$A$9=D2,$B$2:$B$9,0)),B2:B9,0),3)}