VBA to Transpose Data and Loop

Apotts85

New Member
Joined
Apr 26, 2018
Messages
4
Hello Excel Gurus,

I'm trying to write a code that will transform my data from singular columns to multiple rows. My data is currently organized like this;
[TABLE="width: 500"]
<tbody>[TR]
[TD]Index[/TD]
[TD]Budget View[/TD]
[TD]1/1/2018[/TD]
[TD]2/1/2018[/TD]
[TD]3/1/2018[/TD]
[TD]4/1/2018[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Budget[/TD]
[TD]$10.00[/TD]
[TD]$10.00[/TD]
[TD]$10.00[/TD]
[TD]$10.00[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Forecast[/TD]
[TD]$10.00[/TD]
[TD]$0.00[/TD]
[TD]$5.00[/TD]
[TD]$5.00[/TD]
[/TR]
</tbody>[/TABLE]

I need a code that will loop through each row to transform the information into this format. Any help is greatly appreciated. Thanks.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Index[/TD]
[TD]Budget View[/TD]
[TD]Date[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Budget[/TD]
[TD]1/1/2018[/TD]
[TD]$10.00[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Budget[/TD]
[TD]2/1/2018[/TD]
[TD]$10.00[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Budget[/TD]
[TD]3/1/2018[/TD]
[TD]$10.00[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Budget[/TD]
[TD]4/1/2018[/TD]
[TD]$10.00[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Forecast[/TD]
[TD]1/1/2018[/TD]
[TD]$10.00[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Forecast[/TD]
[TD]2/1/2018[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Forecast[/TD]
[TD]3/1/2018[/TD]
[TD]$5.00[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Forecast[/TD]
[TD]4/1/2018[/TD]
[TD]$5.00[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Welcome to MrExcel,

What you are describing is sometimes referred to as Unpivoting data.

What version of Excel are you using? If you have a version that supports Power Query, you can unpivot your data using the Get & Transform operations.
 
Upvote 0
Hello Apotts85,

This macro will transpose the data as you requested. Them macro inputs the data from "Sheet1"and outputs the transposed data to "Sheet2". You can change the sheet names in the macro if you need to. It is assumed row 1 is the header row and the data starts at row 2 on both sheets.

Code:
Sub Transpose()


    Dim Data() As Variant
    Dim j       As Long
    Dim k       As Long
    Dim n       As Long
    Dim Rng     As Range
    Dim RngEnd  As Range
    Dim RngBeg  As Range
    Dim rowCnt  As Long
    Dim wksIn   As Worksheet
    Dim wksOut  As Worksheet
    
        Set wksIn = ThisWorkbook.Worksheets("Sheet1")
        Set wksOut = ThisWorkbook.Worksheets("Sheet2")
        
        Set RngBeg = wksIn.Range("A2")
        Set RngEnd = wksIn.Cells(Rows.Count, "A").End(xlUp)
        
        If RngEnd.row < RngBeg.row Then Exit Sub
            
        Set Rng = wksIn.Range(RngBeg, RngEnd)
        
            rowCnt = RngEnd.row - RngBeg.row + 1
            ReDim Data(1 To (rowCnt * 4), 1 To 4)
            
            For j = 1 To rowCnt
                For k = 0 To 3
                    n = n + 1
                    Data(n, 1) = Rng.Cells(j, 1)
                    Data(n, 2) = Rng.Cells(j, 2)
                    Data(n, 3) = wksIn.Cells(1, 3 + k)
                    Data(n, 4) = Rng.Cells(j, 3 + k)
                Next k
            Next j
        
        wksOut.Range("A2").Resize(n, 4).Value = Data
            
End Sub
 
Upvote 0
Thank you so much Leith! This works perfectly. Would you mind also giving me the code for 12 months? Jan - Dec. Thanks again, you're a genius.
 
Upvote 0
Hello Apotts85,

It has been a hectic Friday but I finally got to the code. Here is the updated macro code. It will now work with any number of columns from "C"onward.

Code:
Sub Transpose()


    Dim colCnt  As Long
    Dim Data()  As Variant
    Dim j       As Long
    Dim k       As Long
    Dim n       As Long
    Dim Rng     As Range
    Dim RngEnd  As Range
    Dim RngBeg  As Range
    Dim RowCnt  As Long
    Dim WksIn   As Worksheet
    Dim WksOut  As Worksheet
    
        Set WksIn = ThisWorkbook.Worksheets("Sheet2")
        Set WksOut = ThisWorkbook.Worksheets("Sheet3")
        
        Set RngBeg = WksIn.Range("A2")
        Set RngEnd = WksIn.Cells(Rows.Count, "A").End(xlUp)
        
        If RngEnd.row < RngBeg.row Then Exit Sub
           
        colCnt = WksIn.Cells(1, Columns.Count).End(xlToLeft).Column - 2
        
        Set Rng = WksIn.Range(RngBeg, RngEnd)
        
            RowCnt = RngEnd.row - RngBeg.row + 1
            ReDim Data(1 To (RowCnt * colCnt), 1 To colCnt)
            
            For j = 1 To RowCnt
                For k = 0 To colCnt - 1
                    n = n + 1
                    Data(n, 1) = Rng.Cells(j, 1)
                    Data(n, 2) = Rng.Cells(j, 2)
                    Data(n, 3) = WksIn.Cells(1, 3 + k)
                    Data(n, 4) = Rng.Cells(j, 3 + k)
                Next k
            Next j
        
        WksOut.Range("A2").Resize(n, 4).Value = Data
            
End Sub
 
Upvote 0
Hello Apotts85,

You're welcome. Good to know it is working like you wanted.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top