Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I convert multiple columns to a single column?

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.

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 618
Default How do I convert multiple columns to a single column?

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.



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 340
Default How do I convert multiple columns to a single column?

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.


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 340
Default How do I convert multiple columns to a single column?

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.


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,180
Default How do I convert multiple columns to a single column?

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.



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 340
Default How do I convert multiple columns to a single 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting Data From One Column into Multiple Columns Justin Hoffmann Excel Worksheet Functions 2 July 12th 06 04:15 PM
Return SEARCHED Column Number of Numeric Label and Value Sam via OfficeKB.com Excel Worksheet Functions 23 January 30th 06 07:16 PM
Positioning Numeric Values Resulting from 6 Column Array Formula Sam via OfficeKB.com Excel Worksheet Functions 2 January 5th 06 03:03 AM
creating a bar graph Johnfli Excel Discussion (Misc queries) 0 October 26th 05 08:16 PM
how to convert multiple columns of data into one single column? Ah 3 Excel Worksheet Functions 1 November 12th 04 03:33 PM


All times are GMT +1. The time now is 06:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"