How to add suffix to duplicates to make them unique

[rominjn]

Hi community,

I have a huge dataset of records each of which is supposed to have its own unique ID. But some rows improperly have the same ID. For example:

Lisa	9045
Sara	9046
Dan	9046
Tim	9046
Jack	9047

How do I write a macro that tells Excel to find the duplicates and then add a suffix to them so that they become unique, so that they look like this?

Lisa	9045
Sara	9046-1
Dan	9046-2
Tim	9046-3
Jack	9047

Thanks.

[Ohma]

Something like this?

Currently have user select the range they want to check for doubles, you might want to put the relevant range in the program itself. Option to just select everything on the sheet is shown as remark, (I used it for a quick test,) but this would also put numbers behind your names and other duplicate data.

Looping like this might not be the fastest option.

Public Sub check_doubles()
Dim counter As Integer
Dim base As String
Dim SearchRange As Range
Dim SearchCell As Range
Dim CompareCell As Range

'Set SearchRange = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
Set SearchRange = Application.InputBox("Select range to be searched", "choose range", Type:=8)
On Error Resume Next
Set SearchRange = SearchRange.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

For Each SearchCell In SearchRange
    If SearchCell.Value <> "" And Not InStr(1, SearchCell.Value, "-") Then
        counter = 1
        base = SearchCell.Value
        For Each CompareCell In SearchRange
            If Not CompareCell.Address = SearchCell.Address And _
            CompareCell.Value = base Then
                If counter = 1 Then
                    SearchCell.Value = base & "-" & 1
                    CompareCell.Value = base & "-" & 2
                    counter = 3
                Else
                    CompareCell.Value = base & "-" & counter
                    counter = counter + 1
                End If
            End If
        Next CompareCell
    End If
Next SearchCell

SearchRange.Select

End Sub

[FDibbins]

You could do this without a macro, using a helper column, and this formula, copied down...
=B2&IF(COUNTIF($B$2:$B$6,B2)>1,+" - "&COUNTIF($B$2:B2,B2),"")

[rominjn]

The issue was urgent so I tried the in-cell formula that FDibbins provided and it worked great. When I have more time, I will try the VBA that Ohma suggested. Thanks to both of you!