Help with converting multiple columns into rows

Punxsu · Aug 24, 2012

Hi Everyone,

I am trying to convert

Excel 2010

	A	B	C	D	E
School	ID	Race	Age	Gender	Group
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]2010[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]2012[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"] 161 [/TD]
[TD="align: right"] 174 [/TD]
[TD="align: right"]307[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]16[/TD]

[TD="align: right"] 174 [/TD]
[TD="align: right"] 192 [/TD]
[TD="align: right"]509[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"] 157 [/TD]
[TD="align: right"] 181 [/TD]
[TD="align: right"]121[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"] 176 [/TD]
[TD="align: right"] 165 [/TD]
[TD="align: right"]106[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]16[/TD]

[TD="align: right"] 186 [/TD]
[TD="align: right"] 173 [/TD]
[TD="align: right"]190[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"] 176 [/TD]
[TD="align: right"] 177 [/TD]
[TD="align: right"]404[/TD]

</tbody>

Data

to

Excel 2010

	A	B	C	D	E	F	G
School	ID	Race	Age	Gender	Group	Year	Size
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"]2010[/TD]
[TD="align: right"] 161 [/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"] 174 [/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"]2012[/TD]
[TD="align: right"] 307 [/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]16[/TD]

[TD="align: right"]2010[/TD]
[TD="align: right"] 174 [/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]16[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"] 192 [/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]16[/TD]

[TD="align: right"]2012[/TD]
[TD="align: right"] 509 [/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"]2010[/TD]
[TD="align: right"] 157 [/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"] 181 [/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"]2012[/TD]
[TD="align: right"] 121 [/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"]2010[/TD]
[TD="align: right"] 176 [/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"] 165 [/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"]2012[/TD]
[TD="align: right"] 106 [/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]16[/TD]

[TD="align: right"]2010[/TD]
[TD="align: right"] 186 [/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]16[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"] 173 [/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]16[/TD]

[TD="align: right"]2012[/TD]
[TD="align: right"] 190 [/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"]2010[/TD]
[TD="align: right"] 176 [/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"] 177 [/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"]2012[/TD]
[TD="align: right"] 404 [/TD]

</tbody>

Results

Any help with a VBA code (possibly with comments) would help. Thanks.

Bert

jimrward · Aug 24, 2012

ok, here is my first effort, i am sure the purists will advise better methods, but I have tried to keep it simple for ease of understanding

Code:

Sub dorow2()
'
'****
' get current sheet name with old data and number of rows of data
'****
'
cursheet = ActiveSheet.Name
intNumRows = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

'
'****
' Add a new sheet and copy the headers from old sheet and add in two new ones
'****
'
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "NewList"

Worksheets(cursheet).Range("A1:F1").Copy Destination:=Worksheets("NewList").Range("A1")
Worksheets("Newlist").Range("G1").Value = "Year"
Worksheets("Newlist").Range("H1").Value = "Size"

'
'****
' using the current sheet stop thru the old data
'****
'
Worksheets(cursheet).Select

newrow = 2
For j = 2 To intNumRows
    With ActiveSheet

'
'****
' get the last year column, could be 9 but some years might be missing
'****
'
        LastCol = .Cells(j, .Columns.Count).End(xlToLeft).Column
        
'
'****
' copy the fixed data, then add the year, and expand
'****
'
        For k = 7 To LastCol
            Worksheets(cursheet).Range("A" & j & ":F" & j).Copy Destination:=Worksheets("NewList").Range("A" & newrow)
            Worksheets("Newlist").Range("G" & newrow).Value = 2003 + k
            Worksheets("Newlist").Range("H" & newrow).Value = Worksheets(cursheet).Cells(j, k).Value
            newrow = newrow + 1
        Next k
    End With
Next j
End Sub

Punxsu · Aug 24, 2012

Hello jimrward,

Thanks for the code. I was able to change a few things to use with my full data--the comments were very helpful. The real data has Years from 2010 to 2050 and over 120,000 rows so it naturally won't fit a single worksheet. I (manually) cut the file into smaller ones (like 7500 rows each) and it freezes. I would like your (or anyone's) suggestion on the matter. Ultimately, I intend to use the final (transposed) file to create Pivot Tables to summarize the file for analyses.

Thanks to you and anyone reading.

Bert

jimrward said:

ok, here is my first effort, i am sure the purists will advise better methods, but I have tried to keep it simple for ease of understanding

Code:

Sub dorow2()
'
'****
' get current sheet name with old data and number of rows of data
'****
'
cursheet = ActiveSheet.Name
intNumRows = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

'
'****
' Add a new sheet and copy the headers from old sheet and add in two new ones
'****
'
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "NewList"

Worksheets(cursheet).Range("A1:F1").Copy Destination:=Worksheets("NewList").Range("A1")
Worksheets("Newlist").Range("G1").Value = "Year"
Worksheets("Newlist").Range("H1").Value = "Size"

'
'****
' using the current sheet stop thru the old data
'****
'
Worksheets(cursheet).Select

newrow = 2
For j = 2 To intNumRows
    With ActiveSheet

'
'****
' get the last year column, could be 9 but some years might be missing
'****
'
        LastCol = .Cells(j, .Columns.Count).End(xlToLeft).Column
        
'
'****
' copy the fixed data, then add the year, and expand
'****
'
        For k = 7 To LastCol
            Worksheets(cursheet).Range("A" & j & ":F" & j).Copy Destination:=Worksheets("NewList").Range("A" & newrow)
            Worksheets("Newlist").Range("G" & newrow).Value = 2003 + k
            Worksheets("Newlist").Range("H" & newrow).Value = Worksheets(cursheet).Cells(j, k).Value
            newrow = newrow + 1
        Next k
    End With
Next j
End Sub

jimrward · Aug 24, 2012

ok, sounds good, the Lastcol piece of code should pick up the extra years

where does the data originate from, is it a text file you import ??

what sort of analysis are you trying to do with pivot tables ??

I am not sure how you are going to proceed with that amount of data when its on different sheets

Punxsu · Aug 24, 2012

The table I am transposing is in Excel. A snapshot of it was what I provided earlier. I am able to create the pivot tables without even transposing the "Year" and "Size" over the rest of the rows but it limits the analyses (that can be done) because Excel then considers the "Year" as values on the row field. The excel data is resident in only one worksheet but when I run the macro (and consequently transposing it),the resulting table will be more than what one excel sheet can hold.

Nonetheless thanks for your help. I really appreciate your input.

jimrward · Aug 24, 2012

Can you post your amended code in case it of use to anyone else, you never know

Punxsu · Aug 24, 2012

It's essentially the same as the one you provided. It is provided below. Thanks.

Public LastColumn As Long, LastRow As Long, NewRow As Long
Dim CurrSheet
Sub Some()
' Get current sheet name with old data and number of rows & columns of data
CurrSheet = ActiveSheet.Name
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
LastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

' Change Headers to Years
ActiveSheet.Range("G1").Value = "2010 Size"
For i = 1 To LastColumn
With ActiveSheet
If InStr(UCase(.Cells(1, i).Value), "SIZE") Then
JustYears = Mid(.Cells(1, i).Value, 1, 4)
.Cells(1, i).Value = JustYears
End If
End With
Next i
End Sub
Sub DoRow()
' Add a new sheet and copy the headers from old sheet and add in two new ones
On Error Resume Next
ScreenUpdating = False
Worksheets("NewList").Delete
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "NewList"

Worksheets(CurrSheet).Range("A1:F1").Copy Destination:=Worksheets("NewList").Range("A1")
Worksheets("NewList").Range("G1").Value = "Year"
Worksheets("NewList").Range("H1").Value = "Size"

' Using the current sheet step thru the old data
Worksheets(CurrSheet).Select

NewRow = 2
For j = 2 To LastRow
With ActiveSheet

' Get the last year column, could be 9 but some years might be missing
LastColumn = .Cells(j, .Columns.Count).End(xlToLeft).Column

' Copy the fixed data, then add the year, and expand
For k = 7 To LastColumn
Worksheets(CurrSheet).Range("A" & j & ":F" & j).Copy Destination:=Worksheets("NewList").Range("A" & NewRow)
Worksheets("NewList").Range("G" & NewRow).Value = 2003 + k
Worksheets("NewList").Range("H" & NewRow).Value = Worksheets(CurrSheet).Cells(j, k).Value
NewRow = NewRow + 1
Next k
End With
Next j
End Sub

Sub CallEm()
Call Some
Call DoRow
End Sub

Peter_SSs · Aug 25, 2012

Punsxu

Welcome to the MrExcel board!

Sorry I didn't get to comment on the code in the other thread you asked about but I'm assuming it was related to this problem so I have commented this code. You should find this code somewhat faster than the other code. I just hope it does what you want, or close enough that you can tweak it or ask for refinements.

Note that in the code the 'Fixed' columns and the 'Year' columns are hard-coded so adjust to suit your data. I guess the year columns could be calculated but I stopped short of that.

 Sub Rearrange() Dim lr As Long, rws As Long Dim fixedcols As Long, swapcols As Long, blocksize As Long Dim i As Long, j As Long, k As Long, z As Long, y As Long, x As Long Dim a, b, Headers Const ConstCols As String = "A:F" '<- Adjust to suit Const ColsToRows As String = "G:I" '<- Adjust to suit ('Year' cols) fixedcols = Columns(ConstCols).Columns.Count swapcols = Columns(ColsToRows).Columns.Count Application.ScreenUpdating = False 'With the original data sheet as the active sheet .. With ActiveSheet 'Collect headings from the 'fixed' columns + 2 more Headers = .Range("A1").Resize(, fixedcols + 2).Value 'Now change the last 2 headings Headers(1, fixedcols + 1) = "Year" Headers(1, fixedcols + 2) = "Size" 'Find how many rows of data we have. Assume headings in row 1 lr = .Cells(.Rows.Count, 1).End(xlUp).Row rws = lr - 1 'Read all the data into an array a = .Range("A1").Resize(lr, fixedcols + swapcols).Value 'Work out how many rows we can process and still fit the results in a worksheet blocksize = IIf(rws * swapcols < .Rows.Count, rws, (.Rows.Count - 1) / swapcols) End With 'Now process each block to go on a new sheet For i = 2 To lr Step blocksize 'Check in case we are up to the last block as it will likely be smaller If i + blocksize > lr Then blocksize = lr - i + 1 End If 'Prepare an arry for the results of this block ReDim b(1 To blocksize * swapcols, 1 To fixedcols + 2) 'Work through each block of data For j = 1 To blocksize 'Temp variable to save recalculating this repeatedly x = i + j - 1 'Do a new result row for each 'year' column For z = 1 To swapcols 'Temp variable to save recalculating this repeatedly y = (j - 1) * swapcols 'Put the basic fixed column data into the results array row For k = 1 To fixedcols b(y + z, k) = a(x, k) Next k 'Now put the year and size data into results array b(y + z, fixedcols + 1) = a(1, fixedcols + z) b(y + z, fixedcols + 2) = a(x, fixedcols + z) Next z Next j 'Add a new sheet to accept the results Sheets.Add After:=Sheets(Sheets.Count) 'Using the newly added sheet With ActiveSheet 'Put the headings in .Range("A1").Resize(, fixedcols + 2).Value = Headers 'Put the results array values into the sheet .Range("A2").Resize(blocksize * swapcols, fixedcols + 2).Value = b 'Adjust the column widths .UsedRange.EntireColumn.AutoFit End With Next i Application.ScreenUpdating = True End Sub

Punxsu · Aug 26, 2012

Hello Peter_SSs,

I am lost for words. I am very impressed and I want to say a big Thank You for your time in developing and commenting the code. It works magnificently and very fastly. I spent a lot of time trying to understand the logic behind the "Processing each block to go on a new sheet". If you have time perhaps you can explain a little further.

It works great. Could you please provide (if you can) some help on how I can create pivot table(s) from the resulting sheets?

Thanks a ton. I am very grateful for your help.

Bertrand

Peter_SSs · Aug 26, 2012

Punxsu said:
Hello Peter_SSs,

I am lost for words. I am very impressed and I want to say a big Thank You for your time in developing and commenting the code. It works magnificently and very fastly.

I'm glad I was on the right track for what you wanted.

I spent a lot of time trying to understand the logic behind the "Processing each block to go on a new sheet". If you have time perhaps you can explain a little further.

I'm not exactly sure what you are asking here but I'll try to explain the determination of the block size. Consider the data in your first post. As there are three 'year' columns (2010, 2011, 2012) each row of original data will occupy 3 rows when rearranged. Now just for a moment, let's pretend that a worksheet only contained 8 rows. In this case we could only convert 2 of your original rows on one sheet since 3 original rows would require 9 rows and we only have 8. So here we would have had to set the block size to 2 rows.

The other general comment is that the code is fast because it uses arrays. That is the data is manipulated in memory, not communucation back and forward between the code in memory and the worksheet itself.

To get a better understanding of the code, I suggest you again use your data from post number 1 and replace the first line below with the second, tricking the code into thinking we can only fit about 12 rows on a sheet (block size of 4 original rows * 3 year rows)

Code:

blocksize = IIf(rws * swapcols < .Rows.Count, rws, (.Rows.Count - 1) / swapcols)

blocksize = 4

Make sure the 'Locals' window is visible within the vba window (View|Locals Window)
Now, with that small sample data sheet active, step through the code a line at a time using the F8 key and keep checking what is happening to the variables in the Locals window.

Could you please provide (if you can) some help on how I can create pivot table(s) from the resulting sheets?

This is not an area I have much expertise in I'm afraid. If you are using Excel 2010, and haven't already, perhaps you should research the new PowerPivot feature. There is a new forum here (PowerPivot Questions.) devoted specially to it.

Help with converting multiple columns into rows

Punxsu

New Member

jimrward

Well-known Member

Punxsu

New Member

jimrward

Well-known Member

Punxsu

New Member

jimrward

Well-known Member

Punxsu

New Member

Peter_SSs

MrExcel MVP, Moderator

Punxsu

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Share this page

Help with converting multiple columns into rows

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock