Transposing Budget line items from Col into Rows ( each Row has to be replicated 12 time for each month

[ravisca2003]

Hello Experts,
I am working on a budget file where users are given an excel template to fill in Budget data for each line item as follows ( I have give

We have another deptt who give us the erp based report where actuals which are booked under the planned head as follows

we are doing this recon in excel and also creating pivot reports to compare planned and actual numbers hence we need to convert our budget format into the actual format. Can someone suggest how we can achieve this

 

[mohadin]

Hi
Try this

Sub test()
Dim a
Dim i&, c&
a = Cells(1).CurrentRegion.Value2
Application.ScreenUpdating = False
Cells(1, 17).Resize(, 5) = Array(a(1, 1), a(1, 2), a(1, 3), "Month", "Amount")
For i = 2 To UBound(a)
        Cells(2 + c, 17).Resize(UBound(a, 2) - 3) = a(i, 1)
        Cells(2 + c, 18).Resize(UBound(a, 2) - 3) = a(i, 2)
        Cells(2 + c, 19).Resize(UBound(a, 2) - 3) = a(i, 3)
        Cells(2 + c, 20).Resize(UBound(a, 2) - 3) = Application.Transpose(Application.Index(a, 1, [{4,5,6,7,8,9,10,11,12,13,14,15}]))
        Cells(2 + c, 21).Resize(12) = Application.Transpose(Application.Index(a, i, [{4,5,6,7,8,9,10,11,12,13,14,15}]))
        c = c + UBound(a, 2) - 3
Next
Application.ScreenUpdating = True
End Sub

 

[alansidman]

Bring your data into the Power Query Editor and Unpivot the Data. If you provide your sample data in an XL2BB format, then we can manipulate the data and demonstrate to you specifically how this occurs.

 

[ravisca2003]

Bring your data into the Power Query Editor and Unpivot the Data. If you provide your sample data in an XL2BB format, then we can manipulate the data and demonstrate to you specifically how this occurs.

SIr , Thanks for your input as well as offer to help further. I am highly obliged. I will use VBA suggestion by mohadin as I have issues of excel version compatibility with power query but again thanks so much for helping

 

[ravisca2003]

Hi
Try this

Sub test()
Dim a
Dim i&, c&
a = Cells(1).CurrentRegion.Value2
Application.ScreenUpdating = False
Cells(1, 17).Resize(, 5) = Array(a(1, 1), a(1, 2), a(1, 3), "Month", "Amount")
For i = 2 To UBound(a)
        Cells(2 + c, 17).Resize(UBound(a, 2) - 3) = a(i, 1)
        Cells(2 + c, 18).Resize(UBound(a, 2) - 3) = a(i, 2)
        Cells(2 + c, 19).Resize(UBound(a, 2) - 3) = a(i, 3)
        Cells(2 + c, 20).Resize(UBound(a, 2) - 3) = Application.Transpose(Application.Index(a, 1, [{4,5,6,7,8,9,10,11,12,13,14,15}]))
        Cells(2 + c, 21).Resize(12) = Application.Transpose(Application.Index(a, i, [{4,5,6,7,8,9,10,11,12,13,14,15}]))
        c = c + UBound(a, 2) - 3
Next
Application.ScreenUpdating = True
End Sub

hi I will try this