Specifying which duplicate entries to remove

[LonelyTower]

Hi all,

The place I am working at currently holds three exams each year. Fo
each exam, the students' results and names and an unique ID ar
recorded within a worksheet. So at the end of each year I would have t
consolidate all the results.

To cut the story short, I copy all the results into one sheet, and us
the Advanced Filter to filter out the duplicates. But I need to kee
the record with the highest score, and discard only the duplicat
entries with the lowest score. How could this be done?

Thanks in advanced

 

[Dave Hawley]

I would use a simple Pivot table.

Lets say A1:A100 have the ID's and B1:B100 have the scores. Both A1 and
B1 are headings

Select any cell in the data

Go to Data>Pivot Table

Accept the default and click Next

Click Layout

Drag the ID field to the Row area

Drag the Score field to the Data area

Double click the Score field while it is in the Data area.

Choose Max then Ok

Finish off the Pivot table.




** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***