CODEWITHSUNDEEP
excelvba
ewuchatka
ewuchatka

Reputation: 33

Convert multiple columns into rows in Excel

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

enter image description here

To enter image description here

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

Upvotes: 1

Views: 481

Answers (1)

YasserKhalil
YasserKhalil

Reputation: 9568

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

Upvotes: 1

Related Questions