Highest Value from Unique range

[Odysseus]

Hi,

Can this be done?

I have a list of registrations with a mileage from external source
EG

Reg Mileage

aa01 1000
aa01 2000
aa01 3000
aa01 4000
bb02 1001
bb02 2001
bb02 3001
bb02 4001
cc03 1002
cc03 2002
cc03 3002
cc03 4002

What i need to be able to do is select the hightest Mileage record fro
each unique reg

So the output needs to look like

aa01 4000
bb02 4001
cc03 4002

Make sense? This report needs to be run once a month for 50 differen
accounts, so I need some sort of macro, or automatic feature that eve
the stupidest of people can use..

 

[Bob Phillips]

=MAX(IF($A$1:$A$100="aa01",$B$1:$B$100))

etc.

this is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jason Morin]

Try a pivot table:

1. Select the data.
2. Go to Data > PivotTable and PivotChart Report
3. Hit Next twice.
4. Press the Layout button.
5. Drag the "Reg" field to the ROW area and the "Mileage"
field to the DATA area.
6. Double-click the "Mileage" filed (it probably reads
as 'Sum of Mileage') and change it to "Max". Press OK.
7. Press OK again and then press Finish.

For more info on working with Pivot Tables, see:

http://www.peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.techonthenet.com/excel/pivottbls/create.htm

HTH
Jason
Atlanta, GA

 

[Odysseus]

Thanks for the response, never thought of using the Pivot tables. Fo
what I need to achive I think it will be the best option.

Cheer