Excel - stack colums

Question

I have a table that was created originally in word that needs to transfer to excel and do the following. Is this possible.

Column 1    Column 2

A1               B1

A2               B2

A3               B3

C1               D1

C2               D2

C3               D3

Needs to become...

Column 1    Column 2     Column 3

A1                A2                A3

B1                B2                B3

C1                C2                C3

D1                D2               D3

Is there any easy way to make this happen?

Answer 1

Enter below formula in cell A11 and copy to the right & down:

=OFFSET($A$2,(ROUNDUP((ROW()-ROW($A$11)+1)/2,0)-1)*3+MOD(COLUMN()-COLUMN($A$11),3),MOD(ROW()-ROW($A$11),2))

You may start at any cell, just replace $A$11 in the formula to the start cell. If you want to start at D2, enter below formula in cell D2 and copy right / down:

=OFFSET($A$2,(ROUNDUP((ROW()-ROW($D$2)+1)/2,0)-1)*3+MOD(COLUMN()-COLUMN($D$2),3),MOD(ROW()-ROW($D$2),2))

Regards,

Amit Tandon

www.globaliconnect.com

Answer 2

snsides,

re: columns to rows

1.  Paste data into Excel, starting at cell A1

2.  Place column 2 directly under column 1

    (row spacing for the entire column should not vary)

3.  Run the code shown below

4.  Sort the new data

CODE...

Assumes data is in column One and runs from row 1 to row 18  (adjust as needed)

'---

Sub AcrossNotDown

Dim N As Long

 For N = 1 To 18 Step 3

 Range(Cells(N, 2), Cells(N, 4)).Value = Application.Transpose(Range(Cells(N, 1), Cells(N + 2, 1)))

 Next

End Sub

'---

IMAGES...

original data

after code is run against the stacked column

after sorting using column 2 as the key


'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)

Answer 3

Hi,

We just want to follow up on your concern. Have you already tried James Cone's suggestion above? How did it go? Let us know if you need more help with formatting the columns in your Excel sheet so that we can look for other steps that you can perform.

We'll wait for your response.

Answer 4

There are over 200 rows of this; it will take forever, was trying to find a better solution.

Answer 5

copy and paste it