Duplicate values in excel

Question

I have a data set in Excel. I need to be able to find the duplicates in Column A, and where there are duplicates choose the highest value in B corresponding to those duplicate values, and then delete any other duplicates in the range of cells. Thanks.

  100602339        500
**100625802**      100
**100625802**   **9000**
  100628819     150000
  100634286        100
  100634286        300
  100635351       5000
  100635383        300
  100635383       1000
  100635383       7500

I'll admit, I'm a complete amateur. I've fumbled with IF and VLookup, but to no avail. I can't even format the question here so it looks similar. — user8538879, Aug 30 '17 at 15:46
What if there are duplicates in A but the value in B is the same (e.g. 100625802 100 / 10065802 100)? — barvobot, Aug 30 '17 at 16:37
I would need to keep the highest of the numbers in column B. (e.g. 100625802 100 / 10065802 100: 100625802 1500 / 10065802 1500). I need 100625802 1500 / 10065802 1500. — user8538879, Aug 30 '17 at 16:49
in order to delete you will need to use VBA. However, if you are willing to generate a new column(s) with just the information you are looking for, then that can be performed with formulas. Basically generate unique list from column A (this includes existing single entries) and pull the maximum corresponding value from column B. — Forward Ed, Aug 30 '17 at 16:59
It appears I made it more difficult then I needed to. If you use conditional formatting and sort by color you can cut and paste the individual A column numbers. Then sort by A smallest to largest, by B largest to smallest and remove duplicates. Thanks for the help. — user8538879, Aug 30 '17 at 17:29

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Formula Option

Desired results are placed in a new columns.

Assuming your list is in range A2:B13

Use the method from this question to to generate your list of unique entries in say D2. Enter the formula and copy down as required. In E3 place the following formula and copy down as required.

D2 CSE formula
=INDEX($A$2:$A$20, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$20), 0))

E2 Regular Formula
=AGGREGATE(14,6,$B$1:$B$11/--($A$1:$A$11=D2),1)

CSE formulas require you to press CONTROL+SHIFT+ENTER when finishing the formula instead of just ENTER. When done right, Excel will automatically add {} around the formula which cannot be added manually.

Duplicate values in excel

1 Answers1

Formula Option