how do you interleave two columns

[Guest]

If you have two columns of data, what's the easiest way to automatically
(programatically?) interleave them. The resulting column should consist of:

1st cell of 1st column
1st cell of 2nd column
2nd cell of 1st column
2nd cell of 2nd column
etc.

For example,
1 a
2 b
3 c

would become:

1
a
2
b
3
c

 

[Guest]

In your selected column (say C), assuming data starts in row 1

in C1: =INDIRECT("A"&INT(ROW()/2)+1)
in C2: =INDIRECT("B"&ROW()/2)

Higlight both and copy down

Copy & Paste Special==>Values if required

HTH

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP

 

[Guest]

Thanks. Instead of 250 how do you get the number of cells in column A or B?
Some kind of length function?

 

[Gord Dibben]

Sub columnmerge()
Dim Lrow As Long
Lrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

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

End Sub


Gord