Move Multiple-Column Data to One Column (not Transpose)

**CriticalEric** · 05-02-2011, 01:52 PM

Greetings. Using Excel 2010. I have 100 customer orders running across 100 columns. Each order has 4 rows of data. I want to maintain the data being in rows but I would like to eventually export/copy+paste one long list to Word or similar. Example:

CURRENTLY
A1 B1 C1 D1 etc.
A2 B2 C2 D2
A3 B3 C3 D3
A4 B4 C4 D4

DESIRED
A1
A2
A3
A4

B1
B2
B3
B4

C1
C2
C3
C4

D1
D2
D3
D4

etc.

Any suggestions?
Eric

**NBVC** · 05-02-2011, 01:59 PM

If you want a space between sets then add a row below all your data with a space (spacebar) in each cell across the row.... then try:

=INDEX($A$1:$D$5,MOD(ROWS($A$1:$A1)-1,5)+1,CEILING(ROWS($A$1:$A1),4)/4)

adjust A1:D5 range to suit

copied down

If you don't want the space between groups, change the 5 to a 4 and don't include blank last row in the reference range.

**CriticalEric** · 05-02-2011, 06:29 PM

NBVC -- Thank you for the quick reply! I tested this out and my colleague asked me to expand it for more than four rows - I am not familiar with these sort of Index-Mod-Ceiling combinations and was unable to expand the formula correctly. Can you show an alternative for 15 rows of data? Below is your original formula that would allow for four lines....

=INDEX($A$1:$D$5,MOD(ROWS($A$1:$A1)-1,5)+1,CEILING(ROWS($A$1:$A1),4)/4)

Regards,
Eric

**NBVC** · 05-03-2011, 07:42 AM

assuming you still want a blank row between groups, then again in the 16th row enter space in each of the cells and then try:

=INDEX($A$1:$D$16,MOD(ROWS($A$1:$A1)-1,16)+1,CEILING(ROWS($A$1:$A1),16)/16)

**CriticalEric** · 05-03-2011, 09:39 AM

Originally Posted by NBVC

assuming you still want a blank row between groups, then again in the 16th row enter space in each of the cells and then try:

=INDEX($A$1:$D$16,MOD(ROWS($A$1:$A1)-1,16)+1,CEILING(ROWS($A$1:$A1),16)/16)

You're the best

I was over-thinking when trying to adapt your formula.

Kind Regards,
Eric