microsoft excel - Transform horizontal table layout to vertical table

The following excel sheet has to be rearranged

from

ID  Name1  Name2  Name3

1   Alf    Bert   Fritz

2   Curt

3   Otto   Mike

4   Sue    Zack 

to

ID  Name

1   Alf

1   Bert

1   Fritz

2   Curt

3   Otto

3   Mike

4   Sue

4   Zack

I am missing the right search term, guess it is fairly common, but I just cannot describe it properly.

Answer

Try this code. It's self explanatory by virtue of working step by step. Your input data starts at A1, and your output starts at A8. This can be changed.

Shorter code is of course possible, and I guess an array formula may be also put together.

Sub single_col()
    Dim icell As Integer
    Dim irow As Integer, icol As Integer
    Dim nrows As Integer, ncols As Integer
    Dim rng_all As Range, rng_curr As Range, rng_trg As Range
    Set rng_all = Range("A2:D5")
    Set rng_trg = Range("A8")
    nrows = rng_all.Rows.Count
    icell = 0
    For irow = 1 To nrows
      Set rng_curr = rng_all.Rows(irow)
      ncols = WorksheetFunction.CountA(rng_curr)
      For icol = 2 To ncols
        icell = icell + 1
        Dim name As String
        name = rng_curr.Cells(1, icol).Text
        rng_trg.Value = rng_curr.Cells(1, 1).Text
        rng_trg.Offset(0, 1).Value = name
        Set rng_trg = rng_trg.Offset(1, 0)
      Next icol
    Next irow
End Sub

PS: code above already includes the modification suggested in the comment.