+ Reply to Thread
Results 1 to 9 of 9

Multiple rows of horizontal data needing to arrange vertically (stacked)

  1. #1
    Registered User
    Join Date
    11-28-2012
    Location
    Kansas City, MO
    MS-Off Ver
    Excel 2011
    Posts
    3

    Multiple rows of horizontal data needing to arrange vertically (stacked)

    I have 500 rows with 6 columns of data (colunms D:I) in each row that need to be arranged vertically on top of each other with the first three columns (Columns A:C) remaining constant using cell references. I realize this will be three separate equations. Please help...Thank You


    SEE ATTACHED SHEET

    Current Data Set:

    A B C D E F G H I
    1 Invoice Date Company Invoice # Account 1 Account 2 Account 3 Account 4 Account 5 Account 6
    2 1/1/12 Vendor 1 987 $1.00 $2.00 $3.00 $4.00 $5.00 $6.00
    3 1/2/12 Vendor 2 654 $7.00 $8.00 $9.00 $10.00 $11.00 $12.00
    4 1/3/12 Vendor 3 321 $13.00 $14.00 $15.00 $16.00 $17.00 $18.00
    etc
    etc
    etc

    Needs to be arranged as follows:

    1 Invoice Date Company Invoice # Amount Account
    2 1/1/12 Vendor 1 987 $1.00 acc1
    3 1/1/12 Vendor 1 987 $2.00 acc2
    4 1/1/12 Vendor 1 987 $3.00 acc3
    5 1/1/12 Vendor 1 987 $4.00 acc4
    6 1/1/12 Vendor 1 987 $5.00 acc5
    7 1/1/12 Vendor 1 987 $6.00 acc1
    8 1/2/12 Vendor 2 654 $7.00 acc2
    9 1/2/12 Vendor 2 654 $8.00 acc3
    10 1/2/12 Vendor 2 654 $9.00 acc4
    11 1/2/12 Vendor 2 654 $10.00 acc5
    12 1/2/12 Vendor 2 654 $11.00 acc6
    13 1/2/12 Vendor 2 654 $12.00 acc7
    Attached Files Attached Files

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple rows of horizontal data needing to arrange vertically (stacked)

    This can be handled more easily I think with a macro. Try this on a copy of your data.

    'ROWS TO COLUMNS
    Here's a macro for expanding rows of data to multiple rows parsing out the values in the row in "groups" while duplicating the first few columns you indicate. There's a sample workbook too you could drop your data into and test it out.

    How/Where to install the macro:

    1. Open up your workbook
    2. Get into VB Editor (Press Alt+F11)
    3. Insert a new module (Insert > Module)
    4. Copy and Paste in your code (given above)
    5. Get out of VBA (Press Alt+Q)
    6. Save as a macro-enabled workbook (*.xlsm)

    The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.


    When you run the macro select column D as the first data row to split down, then answer "3" to how many columns are in each split group. Should work swimmingly.
    Last edited by JBeaucaire; 12-27-2019 at 10:34 PM.

  3. #3
    Registered User
    Join Date
    11-28-2012
    Location
    Kansas City, MO
    MS-Off Ver
    Excel 2011
    Posts
    3

    Re: Multiple rows of horizontal data needing to arrange vertically (stacked)

    Thank you the code works great, however when duplicating the first couple of columns it uses the values instead of the keeping the formulas. I definitely need the formulas to stay. How would I change the code to duplicate the formulas instead of the values?


    The code is as follows:

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 12-03-2012 at 01:13 AM. Reason: Added code tags, as per forum rules. Don't forget!

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Multiple rows of horizontal data needing to arrange vertically (stacked)

    What columns have the formulas in them?
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  5. #5
    Registered User
    Join Date
    11-28-2012
    Location
    Kansas City, MO
    MS-Off Ver
    Excel 2011
    Posts
    3

    Re: Multiple rows of horizontal data needing to arrange vertically (stacked)

    A, B, & C

    Thank you

  6. #6
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Multiple rows of horizontal data needing to arrange vertically (stacked)

    Try this

    Please Login or Register  to view this content.
    Last edited by mike7952; 12-02-2012 at 05:09 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    548

    Re: Multiple rows of horizontal data needing to arrange vertically (stacked)

    there is 3 kinds formula

    first write at A18
    =INDEX(A$3:A$7;TRUNC((ROW(1:1)-1)/6)+1)
    copy it to column B&C then copy down

    second at D18 (amount)
    =INDEX($D$3:$I$7;MATCH(B18;$B$3:$B$7;0);COUNTIF(B$18:B18;B18))
    copy down

    third at E18 (account)
    =INDEX($D$2:$I$2;COUNTIF(B$18:B18;B18))
    copy down
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-19-2011
    Location
    Boston, USA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Multiple rows of horizontal data needing to arrange vertically (stacked)

    Copy range-----> paste special [transpose]

  9. #9
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple rows of horizontal data needing to arrange vertically (stacked)

    @Jpeets - If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1