Automate Adding rows & generating an ID name

[mfitz51]

I need to generate secondary ID numbers (clone ID's) from an initial ID (parent ID). For example, parent ID is ID001. It has 3 clones, which need to be named ID001A, ID001B, ID001C.

My first sheet just has parental ID (1st ID) in column A and number of clones in column B (see image) Some parents have zero clones and should be skipped.

In a new sheet (could also be same sheet if that's easier) I need the parental ID (Column A), new clone ID (Column B). With each clone ID, it will need its own row. I need a code that can generate both the new clone ID (Parent ID + letter in order, (A, B, C, etc.)) and put them in a new row.

If a parent ID has zero clones, it should be skipped over and not included in the new sheet.

I've made a demo of what I'm looking for in the second image. The color coding is not necessary, I just added it to show the parental IDs.
Thank you! This would save me so much time as I am doing this manually right now for hundreds of IDs. If there's anything else I can clarify, please ask!

 

[dreid1011]

Welcome to the forum. This should do what you're after:

Private Sub CloneIDs()
Dim lRowA As Long, lRowB As Long, i As Long, j As Long
Dim wsA As Worksheet, wsB As Worksheet
Set wsA = Sheets("Sheet4") '----Rename your worksheets here
Set wsB = Sheets("Sheet5") '----Rename your worksheets here
lRowA = wsA.Range("A" & Rows.Count).End(xlUp).Row
lRowB = wsB.Range("B" & Rows.Count).End(xlUp).Row + 1
For i = 2 To lRowA
    If Range("B" & i).Value > 0 Then
        For j = 1 To wsA.Range("B" & i).Value
            With wsB
                .Range("A" & lRowB).Value = wsA.Range("A" & i).Value
                .Range("B" & lRowB).Value = wsA.Range("A" & i).Value & Chr(64 + j)
            End With
            lRowB = lRowB + 1
        Next j
    End If
Next i
End Sub

Make sure to rename the sheets.

What happens if an ID has more than 26 clones? How does the suffix change?

 

[mfitz51]

Thank you, that worked perfectly! If they went past the standard alphabet, it would repeat similarly to how excel repeats. (Ex: AA, AB, AC, AD, etc.) More commonly, there is also an instance when these are cloned again and get a numerical added. So, the parent in that case might be ID001A and the next clone would be ID001A1, ID001A2, etc.

 

[dreid1011]

Thank you, that worked perfectly! If they went past the standard alphabet, it would repeat similarly to how excel repeats. (Ex: AA, AB, AC, AD, etc.) More commonly, there is also an instance when these are cloned again and get a numerical added. So, the parent in that case might be ID001A and the next clone would be ID001A1, ID001A2, etc.

Would you like me to adjust the code to account for those instances?