Combine the data in 2 columns of 20 rows into one column of 40 row

I want to combine the data in 2 columns each of 2000 rows in to 1 column of
4000 rows. I want the data to end up in the order A1, B1, A2, B2 and so on.
Any help would be great!
Cheers Tom

Hi Tom,

In column C, I'd do this:
In C1: =A1
In C2: =B1

Then highlight both C1 & C2 and drag down to C4000. The cell references are
relative, so it'll keep repeating as: A1, B1, A2, B2, etc. Then, I'd copy
column C, then Edit-Paste Special-Values to get rid of the formulae.

Cheers,
Pat

"Tom" wrote:

> I want to combine the data in 2 columns each of 2000 rows in to 1 column of
> 4000 rows. I want the data to end up in the order A1, B1, A2, B2 and so on.
> Any help would be great!
> Cheers Tom

Sub Two_To_One()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim R As Long
numRows = 1
For R = 2000 To 1 Step -1
ActiveSheet.Rows(R + 1).Resize(numRows).EntireRow.Insert
Next R
Range("B1").Select
Selection.Insert Shift:=xlDown
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Tue, 2 May 2006 09:39:02 -0700, Tom <Tom@discussions.microsoft.com> wrote:

>I want to combine the data in 2 columns each of 2000 rows in to 1 column of
>4000 rows. I want the data to end up in the order A1, B1, A2, B2 and so on.
>Any help would be great!
>Cheers Tom

Pat

Did you actually test this method?

Try it and see what the results are.


Gord Dibben MS Excel MVP

On Tue, 2 May 2006 09:51:01 -0700, NonIllegitimiCarborundum
<NonIllegitimiCarborundum@discussions.microsoft.com> wrote:

>Hi Tom,
>
>In column C, I'd do this:
> In C1: =A1
> In C2: =B1
>
>Then highlight both C1 & C2 and drag down to C4000. The cell references are
>relative, so it'll keep repeating as: A1, B1, A2, B2, etc. Then, I'd copy
>column C, then Edit-Paste Special-Values to get rid of the formulae.
>
>Cheers,
>Pat
>
>"Tom" wrote:
>
>> I want to combine the data in 2 columns each of 2000 rows in to 1 column of
>> 4000 rows. I want the data to end up in the order A1, B1, A2, B2 and so on.
>> Any help would be great!
>> Cheers Tom

Hi, Pat thanks for trying! its what I tryed first! Excel doesn't like it!
Gord, your reply looks like computer programming to me! If it is how do I go
about using it? Oh I have just thought is it a macro? how do I use it?
Cheers Tom

"Gord Dibben" wrote:

> Sub Two_To_One()
> Application.ScreenUpdating = False
> Dim numRows As Integer
> Dim R As Long
> numRows = 1
> For R = 2000 To 1 Step -1
> ActiveSheet.Rows(R + 1).Resize(numRows).EntireRow.Insert
> Next R
> Range("B1").Select
> Selection.Insert Shift:=xlDown
> Columns("A:A").Select
> Selection.SpecialCells(xlCellTypeBlanks).Select
> Selection.Delete Shift:=xlToLeft
> Range("A1").Select
> Application.ScreenUpdating = True
> End Sub
>
>
> Gord Dibben MS Excel MVP
>
> On Tue, 2 May 2006 09:39:02 -0700, Tom <Tom@discussions.microsoft.com> wrote:
>
> >I want to combine the data in 2 columns each of 2000 rows in to 1 column of
> >4000 rows. I want the data to end up in the order A1, B1, A2, B2 and so on.
> >Any help would be great!
> >Cheers Tom
>
>

Tom

Yes, it is VBA code.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo


Gord

On Tue, 2 May 2006 14:15:01 -0700, Tom <Tom@discussions.microsoft.com> wrote:

>Hi, Pat thanks for trying! its what I tryed first! Excel doesn't like it!
>Gord, your reply looks like computer programming to me! If it is how do I go
>about using it? Oh I have just thought is it a macro? how do I use it?
>Cheers Tom
>
>"Gord Dibben" wrote:
>
>> Sub Two_To_One()
>> Application.ScreenUpdating = False
>> Dim numRows As Integer
>> Dim R As Long
>> numRows = 1
>> For R = 2000 To 1 Step -1
>> ActiveSheet.Rows(R + 1).Resize(numRows).EntireRow.Insert
>> Next R
>> Range("B1").Select
>> Selection.Insert Shift:=xlDown
>> Columns("A:A").Select
>> Selection.SpecialCells(xlCellTypeBlanks).Select
>> Selection.Delete Shift:=xlToLeft
>> Range("A1").Select
>> Application.ScreenUpdating = True
>> End Sub
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Tue, 2 May 2006 09:39:02 -0700, Tom <Tom@discussions.microsoft.com> wrote:
>>
>> >I want to combine the data in 2 columns each of 2000 rows in to 1 column of
>> >4000 rows. I want the data to end up in the order A1, B1, A2, B2 and so on.
>> >Any help would be great!
>> >Cheers Tom
>>
>>

Gord Dibben MS Excel MVP

Top job Gord!
It worked like a charm!
I have lots to learn on excel but I am getting there.
Many thanks -Tom

"Gord Dibben" wrote:

> Tom
>
> Yes, it is VBA code.
>
> If not familiar with VBA and macros, see David McRitchie's site for more on
> "getting started".
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> In the meantime..........
>
> First...create a backup copy of your original workbook.
>
> To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
>
> Hit CRTL + R to open Project Explorer.
>
> Find your workbook/project and select it.
>
> Right-click and Insert>Module. Paste the code in there. Save the
> workbook and hit ALT + Q to return to your workbook.
>
> Run the macro by going to Tool>Macro>Macros.
>
> You can also assign this macro to a button or a shortcut key combo
>
>
> Gord
>
> On Tue, 2 May 2006 14:15:01 -0700, Tom <Tom@discussions.microsoft.com> wrote:
>
> >Hi, Pat thanks for trying! its what I tryed first! Excel doesn't like it!
> >Gord, your reply looks like computer programming to me! If it is how do I go
> >about using it? Oh I have just thought is it a macro? how do I use it?
> >Cheers Tom
> >
> >"Gord Dibben" wrote:
> >
> >> Sub Two_To_One()
> >> Application.ScreenUpdating = False
> >> Dim numRows As Integer
> >> Dim R As Long
> >> numRows = 1
> >> For R = 2000 To 1 Step -1
> >> ActiveSheet.Rows(R + 1).Resize(numRows).EntireRow.Insert
> >> Next R
> >> Range("B1").Select
> >> Selection.Insert Shift:=xlDown
> >> Columns("A:A").Select
> >> Selection.SpecialCells(xlCellTypeBlanks).Select
> >> Selection.Delete Shift:=xlToLeft
> >> Range("A1").Select
> >> Application.ScreenUpdating = True
> >> End Sub
> >>
> >>
> >> Gord Dibben MS Excel MVP
> >>
> >> On Tue, 2 May 2006 09:39:02 -0700, Tom <Tom@discussions.microsoft.com> wrote:
> >>
> >> >I want to combine the data in 2 columns each of 2000 rows in to 1 column of
> >> >4000 rows. I want the data to end up in the order A1, B1, A2, B2 and so on.
> >> >Any help would be great!
> >> >Cheers Tom
> >>
> >>
>
> Gord Dibben MS Excel MVP
>