how to merge many columns to one columnn

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Sorry guys my previous message had errors in it. This is the correct version
of my question, thanks.
 
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
 
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.
 
My header row was the problem otherwise thanks very much it worked and you've
made my load easier.
 
Back
Top