how to merge many columns to one columnn

S

Speedy

I have many cols, same length and containing numeric data. I want to merge
them into one col. The merge should stack all of colB under colA, colC under
colB and so on.

Origainal data is:
A B C D ........
1 2 3 4 ........
. . . . ........
. . . . ........

After merge
A
1
..
..
2
..
..
3
..
..


1 2 3 4
1920 1.1 2.2 3.3 4.3
............................................... 5.7
1921 4.2 3.1 5.6 6.9
................................................ 6.8
1922 5.3 6.2 1.9 2.6
...............................................
...... .... .... .... ....
...............................................
2007 3.2 5.1 2.7 6.5 ................................5.9
 
S

Speedy

Sorry guys my previous message had errors in it. This is the correct version
of my question, thanks.
 
T

T. Valko

Using formulas...

Enter this formula in the first empty cell of column A:

=OFFSET($B$1,MOD(ROWS($A$1:A1)-1,n),INT((ROWS($A$1:A1)-1)/n))

Replace "n" (2 places) with the number of items in your columns.

Copy down until you get a solid sequence of 0s meaning you've gone through
the entire range.

Then you can convert the formulas to constants and delete the original table
if desired (except for the original data in column A).

Select the range of formulas
Goto Edit>Copy
Then Edit>Paste special>Values>OK
 
S

Speedy

It didn't quite do it. What I want is to have all values in colB put under
colA, followed by values of colC and then colD until all columns have been
shifted to the first one column keeping the order of the data values in each
of the shifted columns unchanged. Thank you for helping.
 
S

Speedy

My header row was the problem otherwise thanks very much it worked and you've
made my load easier.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top