Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
=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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
ClearContents method on a passed range | New Users to Excel | |||
named range refers to: in a chart | Excel Discussion (Misc queries) | |||
compare unique identifiers in multiple ranges | Charts and Charting in Excel | |||
UNIQUE | Excel Worksheet Functions |