Finding max value within a subgroup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jconta@gmail.com - 29 Jun 2006 22:00 GMT

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

Reply to this Message

Roger Govier - 29 Jun 2006 22:14 GMT

Hi

With your data in A1:B9, set up a table with Customer numbers in say
D2:D4.
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

Signature

Regards

Roger Govier

> Here's what I'm looking at:
>
[quoted text clipped - 25 lines]
>
> JC

Reply to this Message

jconta@gmail.com - 30 Jun 2006 14:47 GMT

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

> Hi
>
[quoted text clipped - 41 lines]
> >
> > JC

Reply to this Message

Roger Govier - 30 Jun 2006 23:04 GMT

Hi

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

Signature

Regards

Roger Govier

> Worked like a charm - thanks very much.
>
[quoted text clipped - 77 lines]
>> >
>> > JC

Reply to this Message

Roger Govier - 30 Jun 2006 23:11 GMT

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)}

Signature

Regards

Roger Govier

> Hi
>
[quoted text clipped - 82 lines]
>>> >
>>> > JC

Reply to this Message

Biff - 29 Jun 2006 22:28 GMT

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

> Here's what I'm looking at:
>
[quoted text clipped - 24 lines]
>
> JC

Reply to this Message

Biff - 29 Jun 2006 22:34 GMT

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

> Hi!
>
[quoted text clipped - 43 lines]
>>
>> JC

Reply to this Message