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.

"Speedy" wrote:

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

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 EditCopy
Then EditPaste specialValuesOK



--
Biff
Microsoft Excel MVP


"Speedy" wrote in message
...
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

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.

"T. Valko" wrote:

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 EditCopy
Then EditPaste specialValuesOK



--
Biff
Microsoft Excel MVP


"Speedy" wrote in message
...
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

That's exactly what that formula will do. It won't "shift" any data it'll
copy the data then you can "clean it up" as a described.

http://img407.imageshack.us/img407/979/transposect0.jpg

--
Biff
Microsoft Excel MVP


"Speedy" wrote in message
...
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.

"T. Valko" wrote:

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 EditCopy
Then EditPaste specialValuesOK



--
Biff
Microsoft Excel MVP


"Speedy" wrote in message
...
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

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

"T. Valko" wrote:

That's exactly what that formula will do. It won't "shift" any data it'll
copy the data then you can "clean it up" as a described.

http://img407.imageshack.us/img407/979/transposect0.jpg

--
Biff
Microsoft Excel MVP


"Speedy" wrote in message
...
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.

"T. Valko" wrote:

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 EditCopy
Then EditPaste specialValuesOK



--
Biff
Microsoft Excel MVP


"Speedy" wrote in message
...
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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

That's exactly what that formula will do. It won't "shift" any data it'll
copy the data then you can "clean it up" as a described.

http://img407.imageshack.us/img407/979/transposect0.jpg

--
Biff
Microsoft Excel MVP


"Speedy" wrote in message
...
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.

"T. Valko" wrote:

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 EditCopy
Then EditPaste specialValuesOK



--
Biff
Microsoft Excel MVP


"Speedy" wrote in message
...
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