software

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 amp; 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

quot;Tomquot; wrote:

gt; I want to combine the data in 2 columns each of 2000 rows in to 1 column of
gt; 4000 rows. I want the data to end up in the order A1, B1, A2, B2 and so on.
gt; Any help would be great!
gt; 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(quot;B1quot;).Select
Selection.Insert Shift:=xlDown
Columns(quot;A:Aquot;).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlToLeft
Range(quot;A1quot;).Select
Application.ScreenUpdating = True
End SubGord Dibben MS Excel MVP

On Tue, 2 May 2006 09:39:02 -0700, Tom gt; wrote:

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

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 mgt; wrote:

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

quot;Gord Dibbenquot; wrote:

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

Tom

Yes, it is VBA code.

If not familiar with VBA and macros, see David McRitchie's site for more on
quot;getting startedquot;.

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 Insertgt;Module. Paste the code in there. Save the
workbook and hit ALT Q to return to your workbook.

Run the macro by going to Toolgt;Macrogt;Macros.

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

On Tue, 2 May 2006 14:15:01 -0700, Tom gt; wrote:

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

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

quot;Gord Dibbenquot; wrote:

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