Removing Dupes

[JTL9161]

I have a list of names and numbers that i trying to remove a duplicate but keeping the one with the higher value. The names are coming from another listing so each entry is =UPPER(A166)

AAA	10	XXX	9
BBB	9	ZZZ	9
XXX	9	AAA	10
DDD	6	PPP	7
WWW	5	YYY	10
PPP	7	DDD	6
TTT	5	CCC	9
YYY	10	WWW	5
ZZZ	9	BBB	9
CCC	9	TTT	5

There are 2 similar entries for each line but flipped. Example (AAA 10 XXX 9) and (XXX 9 AAA 10). I am looking to eliminate the duplicate and keep the entry with the lower number (if possible) From the listed example
(AAA 10 XXX 9) and (XXX 9 AAA 10) I am loooking to keep the 2nd one (XXX 9 AAA 10) because of the 9 for XXX. After removing the not needed entry I am looking to consolidate the listing down to 5 lines. If both have thes ame numbers (BBB 9 ZZZ 9 & ZZZ 9 BBB 9) I just need 1 of the listing (either one) to stay and not show the other. Hope I explained what I am looking for well enough.

Thank you,
Jamie

 

[DanteAmor]

Hi @JTL9161, thanks for posting on the forum!

The following macro assumes that the data starts at cell A1, if it doesn't, some changes would have to be made to the macro.
It would be more complicated for us to try to make the macros dynamic. In the future, please indicate where your data starts, use the XL2BB tool to put up a minisheet and help us help you.
It also uses column "E" as a helper, if you are using column "E", change the "E" in the code to a column you have available.

So try the following on a copy of your sheet.

Sub RemovingDups_v6()
  Dim c As Range, f As Range, rng As Range
  Dim lr As Long
  
  lr = Range("A" & Rows.Count).End(3).Row
  Set rng = Range("A" & lr + 1)
  
  For Each c In Range("A1", Range("A" & Rows.Count).End(3))
    If Range("E" & c.Row).Value = "" Then
      Set f = Range("C:C").Find(c.Value, , xlValues, xlWhole, , , False)
      Range("E" & f.Row).Value = "x"
      If Range("B" & c.Row).Value <= Range("B" & f.Row).Value Then _
        Set rng = Union(rng, f) Else Set rng = Union(rng, c)
    End If
  Next
  
  rng.EntireRow.Delete
  Columns("E").ClearContents
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------