Add Trailing Count Number For Each Duplicate Value

[Rick Rothstein]

Here is another macro for you to consider...

Sub AffixDuplicateCounts()
  Dim X As Long, LastRow As Long, RepeatCount As Long
  Const StartRow As Long = 5
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = LastRow To StartRow Step -1
    If Application.IsNumber(Cells(X, "A").Value) Then
      RepeatCount = Application.CountIf(Cells(StartRow, "A"). _
                    Resize(X - StartRow + 1), Cells(X, "A").Value)
      If RepeatCount > 1 Then
        Cells(X, "A").Value = Cells(X, "A").Value & Format(RepeatCount - 1, "00")
      End If
    End If
  Next
End Sub