VBA Code to append incremental number to duplicate values to make it unique

[Narendra Babu D]

I have a column with some duplicate values in it. I do not want to eliminate it but append the duplicate text with incremental numbers starting form 1 to make them unique.

 

[Fuji]

If you want the suffix number from the second appearance, use bold part.
Or from the first appearance, use the red line

Sub test()
    With Intersect(Columns("a"), ActiveSheet.UsedRange).Offset(1)  '<--- change "a" to actual column.
        .Replace "_*", "", 2
        .Value = .Parent.Evaluate(Replace("if(#<>"""",#&if(countif(offset(#,,,row(1:" & .Rows.Count & _
        ")),#)>1,""_""&countif(offset(#,,,row(1:" & .Rows.Count & ")),#)-1,""""),"""")", "#", .Address))
        '.Value = .Parent.Evaluate(Replace("if(#<>"""",#&""_""&countif(offset(#,,,row(1:" & _
            .Rows.Count & ")),#),"""")", "#", .Address))
    End With
End Sub