How to remove duplicates but keeping the top one?

[RudeBoy]

In my excel I have a person entered 3 or more times.

Credit score example

Bob Jones 760
Bob Jones 750
Bob Jones 740

I want to remove the duplicate Bob Jones's but keep ONLY the TOP entry which is 760 the Highest. I already have my sheet auto sorting so the top one will always be the HIGHEST

How can I do this wil a formula or button etc etc thanks for your help

 

[sandy666]

if you are able to use PowerQuery you can try this:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Max", each List.Max([Value]), type number}})
in
    #"Grouped Rows"[/SIZE]

edit:
Excel 2010/2013 - PowerQuery add-in
Excel 2016 and higher - PowerQuery built-in

 

[Rick Rothstein]

In my excel I have a person entered 3 or more times.

Credit score example

Bob Jones 760
Bob Jones 750
Bob Jones 740

I want to remove the duplicate Bob Jones's but keep ONLY the TOP entry which is 760 the Highest. I already have my sheet auto sorting so the top one will always be the HIGHEST

Is the name and number all in one cell or is the name in one column and the number in the adjacent column?

 

[RudeBoy]

Is the name and number all in one cell or is the name in one column and the number in the adjacent column?

The first name is in cell "B"
Last name in Cell "C"
Number is in Cell "D"

 

[Rick Rothstein]

The first name is in cell "B"
Last name in Cell "C"
Number is in Cell "D"

Another question...

Is your data sorted so that all of the same names are together in adjacent (vertical) cells? If not, is it possible for the scores to look like this...

Bob Jones 730
Bob Jones 790
Bob Jones 750

and, if so, which row do you want to preserve... the top one with the lowest score or the second row which has the highest score?

 

[RudeBoy]

Another question...

Is your data sorted so that all of the same names are together in adjacent (vertical) cells? If not, is it possible for the scores to look like this...

Bob Jones 730
Bob Jones 790
Bob Jones 750

and, if so, which row do you want to preserve... the top one with the lowest score or the second row which has the highest score?

The TOP Row with the HIGHEST score

 

[Rick Rothstein]

The TOP Row with the HIGHEST score

That actually did not answer any of the questions I asked. Let's try again...

Is your data sorted so that the highest score is always on top?

If your data is not sorted, is it possible for the scores to look like this...

Bob Jones 730
Bob Jones 790
Bob Jones 750

and, if so, which row do you want to preserve... the top one (which has the lowest score) or the second row (which has the highest score)?

 

[jolivanes]

Very simple, easy to adjust macro.
As long as you don't have a very large range to do.

Sub Maybe()
Dim lr As Long, i As Long
lr = Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False
    For i = lr To 3 Step -1
        If Cells(i, 2).Value + Cells(i, 3).Value = Cells(i - 1, 2).Value + Cells(i - 1, 3).Value Then Cells(i, 2).Resize(, 3).Delete Shift:=xlUp
    Next i
Application.ScreenUpdating = True
End Sub