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

[Tom]

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

[NonIllegitimiCarborundum]

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

[Gord Dibben]

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 <[email protected]> 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]

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
<[email protected]> 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

[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 <[email protected]> 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]

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 <[email protected]> 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 <[email protected]> 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

[Tom]

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 <[email protected]> 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 <[email protected]> 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
>