2 columns -> 1 column

[Danny Barak]

Hello all
I have 2 columns and i want to make a 1 column as follows:
a b a
c d b
e f => c
g h d
i j e
.. f
.. g
.. h
.. i
.. j

Any help would be appreciated.
Danny

 

[David McRitchie]

Hi Danny,
On another worksheet you can refer to the
first by it's sheetname. Use the fill-handle to fill down
as far as needed (twice as many rows as in OrigSheet).
=OFFSET(OrigSheet!$A$1,INT((ROW()-1)/2),MOD(ROW()-1,2))

Also see my page
http://www.mvps.org/dmcritchie/excel/snake.col.htm

 

[Gord Dibben]

Danny

Sub CombineCols()
''combine 2 columns to one with data from Col 2 being inserted
''between data from Col 1
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).EntireRow.Select
ActiveCell.EntireRow.Insert
ActiveCell.Select
ActiveCell.Value = ActiveCell.Offset(-1, 1).Value
ActiveCell.Offset(-1, 1).Value = ""
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Gord Dibben Excel MVP