Create list from header and string

Question

Looking for an vba/macro or formula resolution to create lists from headers and string underneath.

See example below:

Existing data is in columns... (B to D) with headers in first row.

Example shown below but range will be dynamic.

I need to create a list with all the headers and sentences aligned. in E and F.

Answer 1

Others will undoubtedly come up with a Microsoft 365 formula solution, or a Power Query solution. In the meantime, here is a short macro:

Sub StackData()
    Dim c As Long
    Dim s As Long
    Dim m As Long
    Dim t As Long
    Application.ScreenUpdating = False
    t = 2
    For c = 2 To 4
        m = Cells(Rows.Count, c).End(xlUp).Row - 1
        Cells(t, 6).Resize(m).Value = Cells(1, c).Value
        Cells(t, 7).Resize(m).Value = Cells(2, c).Resize(m).Value
        t = t + m
    Next c
    Application.ScreenUpdating = True
End Sub

Answer 2

Hi,

In the Query Editor, create an Index column. Right click on the Index column and select "Unpivot Other Columns". Rename the columns. Click on Close and Load.

Hope this helps.

Answer 3

This works just fine.

Thanks again!

...add another Krystal burger!