Highlight and Delete Lower Value of A Subset of Cells

[dco223]

Hi,

I have a huge list of data with numbers in cells. each number also has a corresponding percentage in the next cell over. In some cases, some of the numbers appear twice with differentiating percentages in the cells. What I am trying to do is automatically highlight and delete the duplicate number with the lower of the two percentages. See the below example.

20690 91%
20690 88%

As per above, I would want to delete the duplicate number with the 88% since it is the lower of the two percentages of that number. I have almost 2000 cells and have been going through manually deleting the lower of the two percentages for the duplicate numbers. Is there an easier way to do this? Please see workbook attached.

Thansk!!!

[newdoverman]

This might serve your needs. The entries in column D are the lowest values of the duplicated values. Sort them to the top or bottom of the listing and delete.

[dco223]

Thanks so much! This is exactly what I was looking for. One thing though: i have several unique numbers in the data set as well and this formula seems to return the percentage for them as well. Is there an easy modification to this formula that will exclude numbers that are only entered once?

For example:

298047 90%
298030 67%
298005 98%


Thanks!

[newdoverman]

This uses a helper column to determine duplicates then applies the formula to determine the lowest values of the respective duplicates. This in my limited tests ignores unique values. This is set to handle 10,000 rows....adjust to suit.

[dco223]

Thanks so much! This works perfectly!

[newdoverman]

You're welcome. Thanks for the feedback.

[Xx7]

Here's a pivot table solution. The percentages are ranked from highest to lowest and the pivot table is filtered based on the highest rank.