Help finding and tracking unique duplicate values, append # to name

Something like:

Public Sub AppendSeq(ByVal Target As Excel.Range)

    Dim scpValues           As Object

    Dim arrValues           As Variant
    Dim arrOutput           As Variant

    Dim lngRow              As Long
    Dim intOccur            As Integer
    Dim strOccur            As String

    Set scpValues = CreateObject("Scripting.Dictionary")
    scpValues.CompareMode = TextCompare

    arrValues = Target.Resize(Target.Rows.Count, 1).Value
    ReDim arrOutput(LBound(arrValues, 1) To UBound(arrValues, 1), 1 To 1)
    For lngRow = LBound(arrValues, 1) To UBound(arrValues, 1)
        strOccur = ""
        If scpValues.Exists(arrValues(lngRow, 1)) Then
            intOccur = 1
            While scpValues.Exists(arrValues(lngRow, 1) & " (" & intOccur & ")")
                intOccur = intOccur + 1
            Wend
            scpValues(arrValues(lngRow, 1) & " (" & intOccur & ")") = lngRow
            strOccur = " (" & intOccur & ")"
        Else
            scpValues(arrValues(lngRow, 1)) = lngRow
        End If
        arrOutput(lngRow,1) = arrValues(lngRow, 1) & strOccur
    Next lngRow

    Target.Value = arrOutput

    Set scpValues = Nothing

End Sub

Just pass the range to it. The routine loads the given range values into an array, then loops through the array. The Scripting.Dictionary object is an easy way to check for duplicates, here it first checks if a value has already been added, if so, it increments a counter until it finds one that hasn't. It puts the adjusted value into an output array, then at the end places the output array back into the target values.