Data merging from two cloumns into one

[AMCD]

Hi, I have two colums of data that need to be mereged but i am having some
bother! Initial layout is columa A data (1 - x) and Column B data(1 - x), i
wish to merge them in order to give me a column with a1, b1, a2, b2, a3, b3
and so on.

Any hints or tips?

Cheers Andrew.

 

[Gary''s Student]

In C1 enter:
=A1 & B1 and copy down

 

[AMCD]

Sorry may not have been clear i dont want to merge them into the same cell
but just into one column as shown below. Where Column a and b are the input
with C the output.
Andrew.

A B C
1 a b a
2 c d b
3 e f c
4 d
5 e
6 f

 

[David Biddulph]

=OFFSET($A$1,INT((ROW()-1)/2),MOD(ROW()-1,2))

 

[Max]

One way
In C1:
=OFFSET($A$1,INT((ROWS($1:1)-1)/2),MOD(ROWS($1:1)-1,2))
Copy C1 down as far as required, until zeros appear signalling exhaustion of
data

 

[JLatham]

If you're looking to turn:
a1 b1 into a1
a2 b2 b1
a2
b2

Then try this:
in another column (C?) in first row (at C1) put this formula:
=OFFSET($A$1,ROW()-INT(ROW()/2)-1,0)
in the second row (at C2) put this formula
=OFFSET($B$1,ROW()-INT(ROW()/2)-1,0)

Then just fill those two formulas on down the sheet as far as you need to go.

 

[AMCD]

Thanks very much for the help that worked perfectly Could you tell me how
to do the exact reverse as well, just for future reference. One column into
two ?
Cheers Andrew.

 

[AMCD]

Can you tell me how to do the exact reveerse as well ? one column into 2?
Thanks for the help

Andrew.

 

[David Biddulph]

In B1, =OFFSET($A$1,(ROW()-1)*2,0)
In C1, =OFFSET($A$1,(ROW()-1)*2+1,0)

 

[JLatham]

Assuming information is in column A (I put mine in A21-A39 for this), then in
1st new column put:
=OFFSET($A$21,((ROW()-ROW($A$21))*2),0)
in 2nd new column put:
=OFFSET($A$21,((ROW()-ROW($A$21))*2)+1,0)
and that should get you started. Just change $A$21 to the first cell
address of the actual initial data list.

 

[Max]

.. how to do the exact reverse as well ? one column into 2?

Another option with a single point formula
Assuming source data in A1 down
Place in any starting cell, say in C2:
=OFFSET($A$1,ROWS($1:1)*2-2+COLUMNS($A:A)-1,)
Copy C2 to D2, fill down as far as required,
to return col A into 2 cols in C22 down

And if you want to string it into 3 cols instead,
in C2, copied across 3 cols to E2, filled down:
=OFFSET($A$1,ROWS($1:1)*3-3+COLUMNS($A:A)-1,)


---