How do I convert multiple columns to a single column?

Question

I have a table that looks like this:

Col1     Col2  Col3  Col4
a    b  c  d
e    f  g  h

I need to convert it to this:

Col1     New
a    b
a    c
a    d
e    f
e    g
e    h

I have a ton of data and doing this by hand is out of the question. Does anyone know of a fast way to do this? I'm hoping that there is a built in way (such as paste->transpose or a function?). If not could someone point me at a relevant VBA example since I am little rusty at VBA.

Answer 1

Select the data you want to transform (not including column headings) and run the following macro.

Sub Transform()

    Dim targetRowNumber As Long
    targetRowNumber = Selection.Rows(Selection.Rows.Count).Row + 2

    Dim col1 As Variant
    Dim cell As Range

    Dim sourceRow As Range: For Each sourceRow In Selection.Rows

        col1 = sourceRow.Cells(1).Value
        For Each cell In sourceRow.Cells

            If Not cell.Column = Selection.Column Then
                Selection.Worksheet.Cells(targetRowNumber, 1) = col1
                Selection.Worksheet.Cells(targetRowNumber, 2) = cell.Value
                targetRowNumber = targetRowNumber + 1
            End If

        Next cell

    Next sourceRow

End Sub