How do I incorporate 2 columns into 1 column

[mayhewvb]

All,

My issue is that I have 2 columns of data, Column A and Column B. What
I would like to do is combine these 2 columns into 1 that would result
in something like this:

A1
B1
A2
B2
A3
B3
etc

There are 1000+ entries in each column and I have tried creating a 3rd
column plugging into the cells the following, hoping to create the
ability to click and drag and fill the rest of the relative values:

=A1
=B1
=A2
=B2
=A3
=B3
etc

However, when I click and drag, I am not able to keep the pattern
going. It always seems to revert to something like this after I try to
click and drag the fill box:

A1
A2
A3
A4

Any thoughts on a better way to accomplish this? Or perhaps a tip to
fill the pattern down the column? Sorry if I am missing something
totoally obvious.

Any help is appreciated!

 

[duane]

just pur = a1 in row 1 of your column,

and put this in rows 2 and on

=IF(MOD(ROW(),2)>0,INDIRECT("A"&ROUNDUP(ROW()/2,0)),INDIRECT("b"&ROUNDUP(ROW()/2,0))

 

[Guest]

Another way; use the macro to copy the data into colmn D, then delete columns
A and B

Sub CombineCols()
Dim r As Long, nr As Long, p As Long
Dim dest As Range
On Error Resume Next
Range("A1").Select
nr = ActiveCell.CurrentRegion.Rows.Count
p = Application.CountA("D") + 1
For r = 1 To nr
Set dest = Cells(p, 4)
Cells(r, 1).Copy Destination:=dest
p = p + 1
Set dest = Cells(p, 4)
Cells(r, 2).Copy Destination:=Cells(p, 4)
p = p + 1
Next r
End Sub

 

[Gord Dibben]

You could use this macro.

Sub CombineCols()
''combine 2 columns to one with data from Col 2 being inserted
''between data from Col 1
Range("A1").Select
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