Move Data from Columns to Rows (w/o pivot)

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

cml2011

Thread Starter
Joined
Jan 5, 2011
Messages
7
I have a very large spreadsheet (i.e. too big for pivoting and transposing wouldn't work) which looks something like this:


Col. ACol. BCol. CCol. DCol. ECol. FCol. GEntry1Info124562131654632435435Entry2Info150256874654242454654Entry3Info2221333333336546548463


I want it to look like this (i.e. flip Col.C through Col. G and line it up with Entry1, then do the same for Entry2, etc.):

Col. ACol. BCol. CEntry1Info12Entry1Info456Entry1Info2131Entry1Info6546Entry1Info32435435Entry2Info150Entry2Info25Entry2Info6874654Entry2Info242Entry2Info454654Entry3Info222Entry3Info133333Entry3Info333Entry3Info654654Entry3Info8463

Please help!
 

cml2011

Thread Starter
Joined
Jan 5, 2011
Messages
7
Sorry, I guess my tables didn't show up in the post. Basically I have many columns of data that are specific to one line item. An example would be a monthly budget, where you have a row of all your expenses for March. Instead of March expenses stretching horizontally in columns, I want the expenses to read vertically, so that A1 is "March", B1 is "$100", then A2 is "March" and B2 is "$300", etc.

It's too big to pivot. Your expertise would be most appreciated!
 
Joined
Sep 4, 2003
Messages
4,912
Can you upload a sample workbook? Use the manage attachments button at the bottom of the posting window.

Rollin
 

cml2011

Thread Starter
Joined
Jan 5, 2011
Messages
7
Please see an example attached (the actual spreadsheet is much larger, but this gives a sense!
 

Attachments

Joined
Sep 4, 2003
Messages
4,912
This can easily be done using a macro. I can write the code for you but need to know if the data layout will ever change? Will the number of columns ever change?


Rollin
 

cml2011

Thread Starter
Joined
Jan 5, 2011
Messages
7
Can I run the macro, then copy and paste special/values, then add or delete columns if need be? The one thing that won't change is the item & style combination - i.e. new styles will not be added.
 

cml2011

Thread Starter
Joined
Jan 5, 2011
Messages
7
Hi Rollin, I really appreciate your looking at my data and offering to help me out! If you know of a macro, I would be so very grateful. :)
 

cml2011

Thread Starter
Joined
Jan 5, 2011
Messages
7
Can I add another wrinkle to this? The sheet, when reorganized vertically, will be too big for excel (too many rows). Where "style" is blank, can the macro eliminate the row? If this is add too much complication, I can just break the spreadsheet in half and run a macro on each half separately.
 
Joined
Sep 4, 2003
Messages
4,912
Sorry for the delay. Here is some code to try but please make sure your data is backed up since the macro will be making changes to the existing worksheet and not writing to a new sheet or workbook. The macro code assumes that row #1 contains column headers and there are no blank rows of data in column A. It also assume that you are only using columns A-G as shown in your sample workbook.

Code:
Sub TransPose()

vStartRow = 2  'Change to row number containing first record.
vStartCol = 4  'Change to column number containing first "style" type

Do While Range("A" & vStartRow).Value <> ""

For j = vStartCol To Cells(1, Columns.Count).End(xlToLeft).Column

If Cells(vStartRow, j).Value <> "" Then
vCount = vCount + 1
Rows(vStartRow + vCount).Insert

Range("A" & vStartRow + vCount & ":" & _
Left(Columns(vStartCol - 1).Address(0, 0), 2 + (vStartCol - 1 < 27)) & vStartRow + vCount).Value = _
Range("A" & vStartRow & ":" & "C" & vStartRow).Value

Range("D" & vStartRow + vCount).Value = "Style# " & j - 3
Range("E" & vStartRow + vCount).Value = Cells(vStartRow, j).Value

End If

Next j

Rows(vStartRow).DELETE
vStartRow = vStartRow + vCount
vCount = 0

Loop

End Sub
Regards,
Rollin
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top