I have several spreadsheets with data listed in 4 columns. Each column has
about 1500 rows of data. I need to reorder my data so that column B's data
is placed underneath the last data entry in column A (with column C under B,
etc.)

e.g.

existing order

A B C D
1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4

new order

A
1
2
3
4
1
2
3
4
1
2
3
4
....

Any suggestions would be very much appreciated.

If your first number in column A is in A2, & you want the new column to have
the corresponding number in E2, for example, then use the formula:
=OFFSET(A$2,MOD(ROW()-2,COUNT(A:A)),INT((ROW()-2)/COUNT(A:A)))
and copy down your 6000 rows or as many as you need.
--
David Biddulph

"scottflinders" wrote in message
...
I have several spreadsheets with data listed in 4 columns. Each column has
about 1500 rows of data. I need to reorder my data so that column B's
data
is placed underneath the last data entry in column A (with column C under
B,
etc.)

e.g.

existing order

A B C D
1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4

new order

A
1
2
3
4
1
2
3
4
1
2
3
4
...

Any suggestions would be very much appreciated.

If the data is on sheet1, try:

1. Enter these four text formulas:

'=Sheet1!A1
'=Sheet1!B1
'=Sheet1!C1
'=Sheet1!D1

(Make sure there is a leading apostrophe so it is text and not a
formula)

2. Select these four cells and drag down the fill handle in the lower
right corner of the range however far is needed. The last digit should
increment every four cells.

3. Now choose Data Text to Columns Finish to evaluate the formulas
(assuming tab delimited default).


scottflinders wrote:

I have several spreadsheets with data listed in 4 columns. Each column has
about 1500 rows of data. I need to reorder my data so that column B's data
is placed underneath the last data entry in column A (with column C under B,
etc.)

e.g.

existing order

A B C D
1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4

new order

A
1
2
3
4
1
2
3
4
1
2
3
4
...

Any suggestions would be very much appreciated.

I missed that the data goes under each other.
Do as above but add another column with A,B,C,D which you can sort by
after:

'=Sheet1!A1 A
'=Sheet1!B1 B
'=Sheet1!C1 C
'=Sheet1!D1 D

Lori wrote:

If the data is on sheet1, try:

1. Enter these four text formulas:

'=Sheet1!A1
'=Sheet1!B1
'=Sheet1!C1
'=Sheet1!D1

(Make sure there is a leading apostrophe so it is text and not a
formula)

2. Select these four cells and drag down the fill handle in the lower
right corner of the range however far is needed. The last digit should
increment every four cells.

3. Now choose Data Text to Columns Finish to evaluate the formulas
(assuming tab delimited default).


scottflinders wrote:

I have several spreadsheets with data listed in 4 columns. Each column has
about 1500 rows of data. I need to reorder my data so that column B's data
is placed underneath the last data entry in column A (with column C under B,
etc.)

e.g.

existing order

A B C D
1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4

new order

A
1
2
3
4
1
2
3
4
1
2
3
4
...

Any suggestions would be very much appreciated.

Dragging down A B C D with the fill handle will not repeat the set.
You have to hold down CTRL and then drag the handle.
Sorting will change the references, unless you have
absolute references or values.
Another way is to use reverse Pivot Table. See
http://j-walk.com/ss/excel/usertips/tip068.htm
and sort by Column.

This method worked for me. With A,B,C,D in second column, I selected
the 4x2 range and dragged down then clicked a cell in the second column
followed by AZ sort toolbar button. I like the pivot table way though.

Another way is to use the office clipboard: copy first column (Ctrl+C
twice to activate), then copy second, third and fourth select a cell
and then Paste All.

Herbert Seidenberg wrote:

Dragging down A B C D with the fill handle will not repeat the set.
You have to hold down CTRL and then drag the handle.
Sorting will change the references, unless you have
absolute references or values.
Another way is to use reverse Pivot Table. See
http://j-walk.com/ss/excel/usertips/tip068.htm
and sort by Column.