Convert multiple columns into rows in Excel

Question

How can I transform a file from one format to another: basically Week columns with unit sales become two columns with multiple rows and first few columns (with customer etc info) have multiple rows for unit sales columns columns.

From

To

I think there is a way to do it in Power Query? I am not a very advanced user so would greatly appreciate step by step how to do it :)

Unless there is a macro I can run?

Thanks

Ewa

will try, I think this is exactly what I did in the past and I could not remember :) I found tis and will try to follow. myexcelonline.com/blog/unpivot-data-using-excel-power-query — ewuchatka
– ewuchatka, Commented Mar 1, 2018 at 11:10

YasserKhalil · Accepted Answer · 2018-03-01 11:15:53Z

1

Try this code

Sub Test()
Dim a           As Variant
Dim b           As Variant
Dim i           As Long
Dim j           As Long
Dim k           As Long
Dim x           As Long

a = Sheets("Sheet1").Range("A1:L" & Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row).Value
ReDim b(1 To UBound(a, 1) * 8, 1 To 6)
j = 1

For i = 2 To UBound(a, 1)
    For k = 5 To UBound(a, 2)
        For x = 1 To 4
            b(j, x) = a(i, x)
        Next x
        b(j, 5) = a(1, k)
        b(j, 6) = a(i, k)
        j = j + 1
    Next k
Next i

With Sheets("Sheet1").Range("O1")
    .Resize(, UBound(b, 2)).Value = Array("Account", "Code", "Product", "Segment", "Week", "Unit Sales")
    .Offset(1).Resize(UBound(b, 1), UBound(b, 2)).Value = b
End With
End Sub

answered Mar 1, 2018 at 11:15

YasserKhalil

9,60813 gold badges62 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ewuchatka Over a year ago

Thanks for this, I tried unpivotting via power query and it is done :) Great to know there is also a macro that can do it. Thanks so so much for your swift replies. Much apprecieted

Collectives™ on Stack Overflow

Convert multiple columns into rows in Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related