How to extract rows according to a maximum for a column

[num_gg]

Hi all,

Sorry for this new post dealing with such a classical issue that seems
trivial but that puzzles me:

I have to deal with a 26 columms long Excel file (and about 3 000
rows)

The file has this form (and 23 other columns :gender,address, social
security number ...)


Id Name Value
00001 GATES 345
00001 GATES 322
00003 JOBS 455
00003 JOBS 455
00003 JOBS 435
00003 JOBS 422
00005 GOSLING 123
00005 GOSLING 220
00005 GOSLING 125
00005 GOSLING 125
00008 BALMER 300
00008 BALMER 320

I want to extract from this data another sheet where all refNumbers
(id) must be present,
if possible unique (no multiple rows with the same refNumber) and get
the max Value,
as following:

Id Name Value
00001 GATES 345
00003 JOBS 455
00005 GOSLING 220
00008 BALMER 320

NOTE: As I have to deal with 26 columns, I don't think that a pivot
table is appropriate in this case.
Otherwise, please show me step by step how construct the pivot
table!

Great thanks for any kind of help

Num

 

[Dave Peterson]

I'd sort the data by ID (ascending) and by Value (Descending).

Then select the ID range
Data|Filter|advanced filter|Filter in place and unique records only.

Then only the first ID (highest value) will be visible.

Select that range and copy the visible rows to another sheet.

 

[Roger Govier]

Hi

One way

In cell AA2 enter this array formula
{=IF(A2=A1,"",MAX(IF($A$2:$A$3000=A2,$C$2:$C$3000)))}
and copy down

To enter or edit an array formula use Control+Shift+Enter (CSE) not just
Enter.
Do not type the curly braces { } yourself, if you use CSE Excel will
insert them for you.

Apply a Filter Data>Filter>Autofilter and using the filter on column AA
select (NonBlanks)
Copy the visible rows to another sheet.

 

[num_gg]

I'd sort the data by ID (ascending) and by Value (Descending).

Then select the ID range
Data|Filter|advanced filter|Filter in place and unique records only.

Then only the first ID (highest value) will be visible.

Select that range and copy the visible rows to another sheet.

Quite easy!
That's absolutly what I wanted!

Thanks so much!

Num