So, if I understand correctly, if your original list is in column E, then this code will replicate what you have in columns A:C.

Code:

Sub Convert()
Dim AR() As Variant: AR = Range("E1:E" & Range("E" & Rows.Count).End(xlUp).Row).Value
Dim ro As Integer: ro = 1
Dim cnt As Integer: cnt = 1
Dim tChar As Integer: tChar = 65
Dim b As Boolean: b = False
For i = LBound(AR) To UBound(AR)
If cnt = 1 Then Cells(ro, 1) = "Team" & Chr(tChar)
If cnt < 6 Then Cells(ro, 2) = cnt
If AR(i, 1) = vbNullString Then
For j = cnt To 5
Cells(ro, 2).Value = cnt
cnt = cnt + 1
ro = ro + 1
Next j
Else
Cells(ro, 3) = AR(i, 1)
End If
If cnt > 5 Then
cnt = 0
tChar = tChar + 1
End If
ro = ro + 1
cnt = cnt + 1
Next i
If cnt < 6 Then
For k = cnt To 5
Cells(ro, 2).Value = cnt
cnt = cnt + 1
ro = ro + 1
Next k
End If
End Sub

## Like this thread? Share it with others