Trying to recreate existing macro for assigning serial numbers to duplicate data entries

[Rosenshredder]

Hello,
Let me begin by saying I am a beginner when it comes to macros in excel.

I have an excel sheet with a password protected macro that I am trying to duplicate. Disclaimer: I own the original file and have legal permission to use it as needed, but the passwords have been long forgotten and all efforts to retrieve them were futile.

What I am trying to do, is add a unique serial number (or letter) to each duplicate entry within a column. I'd like that serial number to be in the same column as well.

The current macro I am trying to duplicate functions like this: (Note, each entry is 4 numbers. Even the first unique data entry receives a serial number. In this case a serial number

1234A

9999A

1234B

1234C

9999B

1234D

<tbody>
</tbody>

This seems like it should be simple, but being so new to macros and being unable to find any similar examples online, I've turned to you guys for help. Any insight would be helpful, or if I am missing important info please let me know so I can add it.

Thanks so much.

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming
- you will not have more than 26 repeats in the data (so A-Z will be sufficient to append),
- data in column A, starting at row 2
try this on a copy of your data.

Sub AppendLetters()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  Dim s As String
  
  Set d = CreateObject("Scripting.Dictionary")
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      s = a(i, 1)
      If Len(s) > 0 Then
        d(s) = d(s) + 1
        a(i, 1) = s & Chr(64 + d(s))
      End If
    Next i
    .Offset(, 1).Value = a
 End With
End Sub

 

[Rosenshredder]

Thank you very much! I'll give that a try this morning as I have been out of the office for a few days.

I'll just add one detail. Currently the sheet I am trying to duplicate will go from tag Z to AA, AB, AC, etc. However, in a practical application of this spreadsheet, there is no reason any number should be duplicated more than 3 times, let alone 26.

I'll let you know how your macro worked for me. Thanks again!