change everything to one column

J

jbly

I have two columns of data that I want to put into one column, alternating
the second column cells between the first column cells.
Like this:
A1
A2
B1
B2
C1
C2

Please help.
 
M

Max

I'll presume you have 2 source rows of data (in rows 1 and 2)
viz: A1,B1,C1, ... and A2,B2,C2, ...

and you want it extracted in one col like this:
A1
A2
B1
B2
C1
C2
Click to expand...
etc

Just place this in any cell below the 2 source rows, say in A4:
=OFFSET($A$1,MOD(ROW(A1)-1,2),INT((ROW(A1)-1)/2))
Copy A4 down as far as required
 
D

David Biddulph

What you've listed are two rows of data, not two columns. Which do you want
to process?
 
J

Joel

I did it. It is not simple. You have to know modlue arithmetic and the
offset command in excel.

I put the following data into the spreadsheet
A B C D E
1 1 3 5 7 9
2 2 4 6 8 10



Then I put this formula into A12
=OFFSET($A$1,MOD(ROW(A12),2),2*(ROW($A$1)-1)+((ROW(A12)-ROW($A$12))/2))


Then I copied from: A12 to: A13 to A21


=OFFSET($A$1,2*(ROW($A$1)-1)+((ROW(A12)-ROW($A$12))/2),MOD(ROW(A12),2))
 
D

Dave Peterson

Put this in C1:
=INDEX(A:A,(ROW()+1)/2)
Put this in C2:
=INDEX(b:b,(ROW()+1)/2)

Select C1:C2
and drag down until you run out of data.

And maybe....

Select column C
edit|copy
edit|Paste special|Values

and delete columns A:B
 
J

jbly

I believe that is two columns, correct? My example has 3 rows with 2 columns.
Make
A1 A2
B1 B2
C1 C2

into

A1
A2
B1
B2
C1
C2

I'll try the other response to see if it works. Thanks for your help.
 
J

jbly

Sorry, you're right. I have column and row headings backwards. It should be
A1
B1
A2
B2
A3
B3

I thought the letters were rows, my bad.
 
M

Max

Put this in say, C1:
=OFFSET($A$1,INT((ROW(A1)-1)/2),MOD(ROW(A1)-1,2))
Copy C1 down as far as required
 
Top