1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by cml2011, Jan 5, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. cml2011

    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!
     
  2. cml2011

    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!
     
  3. Rollin_Again

    Rollin_Again

    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
     
  4. cml2011

    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!
     

    Attached Files:

  5. Rollin_Again

    Rollin_Again

    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
     
  6. cml2011

    cml2011 Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    7
    Yes, perhaps the number of columns will change down the line.
     
  7. cml2011

    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.
     
  8. cml2011

    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. :)
     
  9. cml2011

    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.
     
  10. Rollin_Again

    Rollin_Again

    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
     
  11. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/972917

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice