Filtering "maximum" rows

[Ciccio Tom]

Hi all

I need to extract the rows that store the maximums, by a column
B, between those rows having the same column 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
800000444444 | 1260
800000444444 | 5660
800000444444 | 1260


I want to get a new sheet with

Column A | Column B
800000111111 | 27000
800000222222 | 15000
800000333333 | 9800
800000444444 | 5660

Thanks for help

Tom

 

[Bob Umlas]

if you place the unique fields in FieldA in F2:F5, for example, then in G2
you can Ctrl/Shift/Enter this:
=MAX(IF(F2=$A$2:$A$100,$B$2:$B$100))
and fill down to G5.

 

[Peo Sjoblom]

If you mean using a filter you can use data>filter>autofilter, assume your
data (not header) starts in A4
with 800000111111 and in B4 with 27000 in C4 put


=AND(SUMPRODUCT(--($A$4:A4&$B$4:B4=A4&B4))=1,MAX(($B$4:$B$18)*($A$4:$A$18=A4))=B4)

entered with ctrl + shift & enter

copy down as long as needed, apply the filter on all 3 ranges and filter on
TRUE in column C will get you

Field A Field B
8000001111 27000 TRUE
8000002222 15000 TRUE
8000003333 9800 TRUE
8000004444 5660 TRUE


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Ciccio Tom]

Let me explain it better.

My worksheet has 64000 rows.
I need a script or macro that filters all rows in one time, I cannot go
editing a formula row by row neither group by group.

thanks