Advanced Filtering: Find record with highest amount

  • Thread starter Thread starter John
  • Start date Start date
J

John

How can I construct a criteria that finds the records containing the
highest amount for each month? Example dataset:

May, John, 100
May, Ted, 200
May, Beth, 210
Jun, John, 500
Jun, Ted, 100
Jun, Beth, 400

Desired filtered dataset:
May, Beth, 210
Jun, John, 500

I know about the MAX function, butI can't figure out how to find the
MAX at each change in the month value. Thanks for any suggestion!
 
I don't know if filtering can help, but here is a simple trick:

Sort the dataset by amount descending. Then use VLOOKUP to find data
related to May. VLOOKUP will find the first MAY record, but because of the
sort, the first record will also be the max for that month.
 
"John" askes...
How can I construct a criteria that finds the records containing the
highest amount for each month? <cut>
Click to expand...

With Subtotals you can create a statistical value on a field for a group of
records.
You want to find the max (statistical value or aggregate) amount (field) for
each month (group of records).

First make sure that you have gouped the records. In your case, you need to
sort ascending in the column with the month-names. All records of the same
month are now grouped together. Now what you want is Excel to do is to find
the hifghest amount when the first month changes int the second month and so
on.
This is exactly what Subtotals does.

Choose menu Data -- Subtotals.

In the window choose: at each change in Month, uw function Max, add subtotal
to Amount.

Good luck,

Sybolt
 
Sybolt,

I've got the subtotals with Max working, but is there a way to show
the name with the max subtotal? For example, subtotal shows:
May, 210

I'd like to see:
May, Jane, 210

Is this possible using the subtotal function? Thank you...
 
I finally got it.

In a helper column, say column D enter in D2:

=IF(C2=SUMPRODUCT(MAX(--(C$2:C$37)*(A$2:A$37=A2))),1,0)
and copy down. Here is some sample data:

month person value super
apr larry 42 0
oct moe 47 1
jul curley 29 0
nov manny 75 1
jan moe 11 0
jul jack 78 1
jun rocky 44 0
jul bullwinkle 56 0
jan larry 20 0
mar moe 67 1
jun curley 90 1
oct manny 14 0
set moe 66 1
mar jack 61 0
nov rocky 71 0
nov bullwinkle 43 0
may larry 58 0
apr moe 81 1
aug curley 47 0
may manny 73 1
feb moe 86 1
oct jack 11 0
apr rocky 25 0
aug bullwinkle 50 1
dec larry 31 0
sep moe 79 1
mar curley 54 0
feb manny 52 0
jun moe 80 0
feb jack 82 0
set rocky 14 0
jan bullwinkle 85 1
aug larry 14 0
may moe 22 0
dec curley 40 0
dec manny 63 1

As you see, the helper column has a 1 for the max for each month. Just
switch on AutoFilter and select 1 to see the complete rows with the max for
each month.
 
Back
Top