Highest Value from Unique range
 

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 from
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 different
accounts, so I need some sort of macro, or automatic feature that even
the stupidest of people can use...


--
Odysseus
------------------------------------------------------------------------
Odysseus's Profile: http://www.excelforum.com/member.php...o&userid=14563
View this thread: http://www.excelforum.com/showthread...hreadid=320085

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


"Odysseus" wrote in message
...

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 from
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 different
accounts, so I need some sort of macro, or automatic feature that even
the stupidest of people can use...


--
Odysseus
------------------------------------------------------------------------
Odysseus's Profile:
http://www.excelforum.com/member.php...o&userid=14563
View this thread: http://www.excelforum.com/showthread...hreadid=320085

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

-----Original Message-----

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 from
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 different
accounts, so I need some sort of macro, or automatic
feature that even
the stupidest of people can use...


--
Odysseus
---------------------------------------------------------
---------------
Odysseus's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=14563
View this thread:
http://www.excelforum.com/showthread...hreadid=320085

.

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

Cheers


--
Odysseus
------------------------------------------------------------------------
Odysseus's Profile: http://www.excelforum.com/member.php...o&userid=14563
View this thread: http://www.excelforum.com/showthread...hreadid=320085