CODEWITHSUNDEEP
excel
Cosmic Hawk
Cosmic Hawk

Reputation: 217

How do I remove all duplicates in one column, and keep the row with highest value (from another column)

I'm trying to remove duplicate CustomerIDs and keep the one with the highest CompositeRank. 

CUSTOMERID     STORE      COMPOSITERANK
51872          FOS        5
51872          MOS        3
51872          BOS        2
10098          BOS        5
10098          MOS        3
10098          FOS        2
99102          MOS        5
99102          FOS        3
99102          BOS        2

After removing the proper duplicates, it should look like this: 

CUSTOMERID     STORE      COMPOSITERANK
51872          FOS        5
10098          BOS        5
99102          MOS        5

Thanks for your time.

Upvotes: 0

Views: 73

Answers (1)

xQbert
xQbert

Reputation: 35323

Multi step approach:

  1. Sort by customerID then compositerank Desc
  2. Add a formula similar to the following =IF(A2=A1,0,1) 3)
  3. add a filter for 1 achieve results similar to below screenshot.

enter image description here

If you need to retain ordering first add a column with 1 and then 1 + row above and fill the series down. Then after step 3, re-order based on column added.

you can then copy /paste results to different worksheet if needed, or just leave the non-highest ones hidden.

Upvotes: 1

Related Questions