Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Turn Horizontal Date to Vertical (Excel 2003)

    I have budget file that is laid out horizontally. I need to turn it into a horizontal layout. Hopefully the before and after I paste below will explain.
    Account Desc Year Jan Feb Mar
    7660 Auto 2008 80 80 80
    3450 Fees 2008 100 100 90
    7670 Gifts 2008 220 220 150
    3150 Consult 2008 1000 1000 3200

    After, should look like:

    Account Desc Year Month Amount
    7660 Auto 2008 Jan 80
    7660 Auto 2008 Feb 80
    7660 Auto 2008 Mar 80
    3450 Fees 2008 Jan 100
    3450 Fees 2008 Feb 100
    3450 Fees 2008 Mar 9000
    7670 Gifts 2008 Jan 220
    7670 Gifts 2008 Feb 220
    7670 Gifts 2008 Mar 150
    3150 Consult 2008 Jan 1000
    3150 Consult 2008 Feb 1000
    3150 Consult 2008 Mar 3200

    I realize that the headers "Month" and "Amount" would really show up but I can add that. Copying and transposing data doesn't do it--not I could see. I might could manipulate a pivot, and I could probably make that work, but have come into this issue before and was looking perhaps for some kind of coding solution that could be adapted.

    Any help appreciated.
    Steve

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Turn Horizontal Date to Vertical (Excel 2003)

    How about: [I presume you will want to change iMonths to 12 to work with jan - Dec and go out to col O.]

    <pre>Option Explicit
    Sub ColToRow()
    Dim wksS As Worksheet
    Dim lRowS As Long
    Dim lRowsS As Long
    Dim iCol As Integer
    Dim iColMonth As Long
    Dim wksD As Worksheet
    Dim lRowD As Long
    Dim iMonths As Integer
    Dim iDupCol As Integer

    iDupCol = 3 'Number of colums to duplicate (A-C)
    iMonths = 3 'Number of Columns to compress (D-F)
    'Change iMonths to 12 for D-O if Jan-Dec
    Set wksS = Worksheets("Sheet1") 'Change as appropriate
    Set wksD = Worksheets.Add

    With wksS
    lRowsS = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row
    lRowS = 1
    For iCol = 1 To iDupCol
    wksD.Cells(1, iCol) = .Cells(1, iCol)
    Next
    wksD.Cells(1, iDupCol + 1) = "Month"
    wksD.Cells(1, iDupCol + 2) = "Amount"
    lRowD = 2
    For lRowS = 2 To lRowsS
    For iColMonth = 1 To iMonths
    For iCol = 1 To iDupCol
    wksD.Cells(lRowD, iCol) = .Cells(lRowS, iCol)
    Next
    wksD.Cells(lRowD, iDupCol + 1) = .Cells(1, iColMonth + iDupCol)
    wksD.Cells(lRowD, iDupCol + 2) = .Cells(lRowS, iColMonth + iDupCol)
    lRowD = lRowD + 1
    Next
    Next
    End With

    Set wksS = Nothing
    Set wksD = Nothing
    End Sub</pre>


    Note: I get the output line:
    3450 Fees 2008 Mar <font color=red>90</font color=red>

    Not:
    3450 Fees 2008 Mar <font color=red>9000</font color=red>

    Since you have listed:
    3450 Fees 2008 100 100 <font color=red>90</font color=red>

    not:
    3450 Fees 2008 100 100 <font color=red>9000<font color=red>
    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>


    Steve

  3. #3
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Turn Horizontal Date to Vertical (Excel 2003)

    <img src=/S/bow.gif border=0 alt=bow width=15 height=15> Perfect! Sweet! uhh...yes, looks like I made a few keying errors <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    This worked very well! You assumed correctly on the need to carry out out for 12 months. If I understand this correctly, it can be adapted not only for the 12 months of data, but if I had 4 "lead" columns of data, or 'Number of columns to duplicate. Very nice.

    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Turn Horizontal Date to Vertical (Excel 2003)

    It could be done but would be more complex if the pattern were not the same. I presumed "lead columns" (and allow to pick the numbe") which are duplicated for each of the "individual columns"

    The number for each can change but I presumed that all the lead columns would be used and then the individual columns would show up with the header and the value

    I am glad I could help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •