First, sort your data by name, and remove any blank rows (if
any). Then, run the following code.

The code makes the following assumptions:
1) Source data is on Sheet1, column A, with no blanks.
2) The transformed data is written to Sheet2.
3) No single name has more than 255 accounts.

Sub XForm()

Dim SRng As Range
Dim DRng As Range
Dim SaveVal As String

Set SRng = Worksheets("Sheet1").Range("A1")
Set DRng = Worksheets("Sheet2").Range("A1")

Do Until SRng.Value = ""
If SRng.Value < SaveVal Then
Set DRng = DRng.Parent.Cells(DRng.Row + 1, 1)
DRng.Value = SRng.Value
SaveVal = SRng.Value
End If
Set DRng = DRng(1, 2)
DRng.Value = SRng(1, 2).Value
Set SRng = SRng(2, 1)
Loop

End Sub