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

1. ## 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

2. ## 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:

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.

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.``

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

What columns have the formulas in them?

A, B, & C

Thank you

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

Try this

``Please Login or Register  to view this content.``

7. ## 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

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

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

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

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