Select Distinct Maximums

[Zeepowlee]

Hi all

I need to extract from a workbook the rows who are the maximums, by a field
B, between those having the same field A (numeric, length 12).

I.E:

Field A | Field B
800000111111 | 27000
800000111111 | 4000
800000222222 | 5000
800000222222 | 8000
800000222222 | 15000
800000222222 | 2000
800000222222 | 15000
800000222222 | 9000
800000222222 | 9000
800000333333 | 4500
800000333333 | 2700
800000333333 | 9800

I want to get a new sheet with
Field A | Field B
800000111111 | 27000
800000222222 | 15000
800000333333 | 9800


Thanks for help

 

[Guest]

I know you posted your request in the Worksheet Functions area, but I have to
mention that a Pivot Table would easily handle your request.

Select your data
Data>Pivot Table
Use Excel
Select your data
Click the [Layout] button

ROW: Drag the Field_A field here
DATA: Drag the Field_B field here
If it doesn't list as MAX of Field_B...dbl-click it and set it to Max
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each Field_A and the respective Max of Field_B.
To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Zeepowlee]

Hi all

I need to extract from a workbook the rows who are the maximums, by a
field B, between those having the same field A (numeric, length 12).

I.E:

Field A | Field B
800000111111 | 27000
800000111111 | 4000
800000222222 | 5000
800000222222 | 8000
800000222222 | 15000
800000222222 | 2000
800000222222 | 15000
800000222222 | 9000
800000222222 | 9000
800000333333 | 4500
800000333333 | 2700
800000333333 | 9800

I want to get a new sheet with
Field A | Field B
800000111111 | 27000
800000222222 | 15000
800000333333 | 9800

Nobody can help?


thanks

 

[Pete_UK]

Can you not see Ron's reply? (I ask because I have found recently that
some of my postings are not visible on some newsgroup sites)

Pete