merge columns

[Guest]

i have two columns A & B filled with 10 rows of data. I want to create
column C which should take values from columns A & B one below the other.

i.e C1 should contain A1, C2 should contain B1, C3 should contain A2, C4
should contain B2,....C19 should contain A10 and C20 should contain B10.

Pl. help to form this column C easily. My work is stuck up because of this.
I 've the same type of work to be done for several sets of columns.

Pl. help

 

[Biff]

Hi!

Try this:

=OFFSET($A$1,INT((ROWS($A$1:A1)-1)/2),MOD(ROWS($A$1:A1)-1,2))

Copy down 20 rows.

Biff

 

[Guest]

Hi karpagam

or do the whole thing with a macro:

Sub columnmerge()

For i = 1 To 250
ActiveSheet.Cells(i, 3) = ActiveSheet.Cells((i + 1 - ((i + 1) Mod 2)) /
2, ((i + 1) Mod 2) + 1)
Next i

End Sub

cheers Carlo

 

[Biff]

Here's another one that doesn't use volatile functions:

=INDEX(A$1:B$10,INT(ROWS($A$1:A2)/2),MOD(ROWS($A$1:A2),2)+1)

Biff