How do I incorporate 2 columns into 1 column

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!


-- 
mayhewvb
------------------------------------------------------------------------
mayhewvb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24352
View this thread: http://www.excelforum.com/showthread.php?threadid=379488

0
6/15/2005 8:50:58 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
769 Views

Similar Articles

[PageSpeed] 23

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))

--
duan

-----------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1162
View this thread: http://www.excelforum.com/showthread.php?threadid=37948

0
6/15/2005 9:11:02 PM
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: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

"mayhewvb" wrote:

> 
> 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!
> 
> 
> -- 
> mayhewvb
> ------------------------------------------------------------------------
> mayhewvb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24352
> View this thread: http://www.excelforum.com/showthread.php?threadid=379488
> 


0
6/15/2005 11:25:14 PM
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

On Wed, 15 Jun 2005 15:50:58 -0500, mayhewvb
<mayhewvb.1qormc_1118869503.332@excelforum-nospam.com> wrote:

>
>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!

0
Gord
6/16/2005 2:10:57 AM
Reply:

Similar Artilces:

MAPISP32 #2
MAPISP32 has taken over - uses almost all of the CPU (97 to 99%). Can't send or receive email. Restarting and not launching OUTLOOK gives me back my computer, but no email. Using OUTLOOK 97 on an XP machine. Have checked the postings, but found nothing about this problem (I get no error messages). Also tried renaming MAPISP32.dll and then reloading OUTLOOK, no improvement. Will check back later and thanks! ...

Beginning Inventory Balance #2
I am trying to duplicate the Average Inventory, according to KB856731, the formula is; (Beginning Inventory Balance + Summary of each months ending on hand Inventory + Current On Hand Inventory) / (number of months elapsed in the current year +2) Does anyone know what the formula is for the Beginning Inventory Balance? -- Anthony ...

Macro Security #2
Hi, I am trying to set the Macro security level to Low in Access 2003 but cannot find the option in the Tools\Macro menu. It is not even grayed out. The install is a complete install of Office 2003 and I tried a Detect and Repair without luck. Any suggestions? Thank you. Maybe a post in one of the Access newsgroups would get you an answer quicker. Clementius wrote: > > Hi, > I am trying to set the Macro security level to Low in Access 2003 but cannot > find the option in the Tools\Macro menu. It is not even grayed out. The > install is a complete install of Office 2003 ...

Spaces issue not fixed in 12.2
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel The previous threads have all been closed and none of them seem to have an answer. For anyone else struggling with Office not working with Spaces (and therefore having to disable Spaces to use Office 2008), here is the reply I just received from MS Office for Mac Tech Support: "The Spaces issue is still being worked on, please watch for future OS and Office updates" My guess is that as it has not been fixed in two service packs it is unlikely to change until Office 2012 (or whatever the next major release wil...

Control can't be edited; it's bound to a replication system column 'TableName'.
I have an Access 2007 accdb database. It hast a table in it with field names TableName and FieldName and a bunch of others. This table has had a small number of records added to it (37) over the life of the database. Suddenly, the two fields TableName and FieldName cannot be edited. That is even if the table is open as a table and you try to type a new record. When you do that an error message is briefly displayed in the left hand end of the Access main window's status bar. For example if you try to input anything into the TableName field you will be blocked and you will briefl...

Preventing Column Cell Values from Printing
I have a summary worksheet with several columns of information. There is one particular column (E25:F39) where I don't always want to print the values. Is there some simple way to toggle this column's values on and off just before hitting the print command? I was thinking of using an adjacent tick box outside the print area, which when ticked would print the column values, and vice-versa. I'm looking for a formula that would examine the tick box, and if the tick (letter 'a' in Marlett font) is not present (i.e. cell value = ""), then the values in column (E25:...

Conditional Formatting #2
Hello, I have a spreadsheet that has about 1200 rows many of which need to have conditional formatting. I had a template set of rows that I was transferring the conditional formatting from and it worked for 20 or so rows. Then in attempting to both copy/paste and drag formatting even further I noticed that the conditional formatting was not being transfered to the target cells. I have even tried doing one row of formatting at a time but it seems like it will not allow any more conditional formatting. The spreadsheet is a good size (about 2MB). I recieve no error messages about this. It simpl...

How do I force all capital in a column of lower case names?
I read the help, but it doesn't tell me where to enter the formula. If you do not have a blank column next to the mentioned column, then insert one. Enter the formula in a cell adjacent to the cell you want to change, and copy down. Then copy this column, go to the original column, select a cel, right click and click on Paste Special, tick values. Delete the helper column. Iow, with you lower case data in Column E, click on Column F, if not empty, and insert a column. F is then an empty column. Say your data starts in E2, then in F2 enter =UPPER(E2). Move cursor to the botto...

How to find a value with multi-column, multi-record list
I have a spreadsheet with $costs arranged according to values in both rows and columns. How do I create drop down lists for both the rows and columns and then return the cell value? eg if I have 5 rows (2-6) and 6 columns (B-G) with costs arranged in each cell within this group how do I create drop downs for each selection, 2-6 and B-G, and return the value to a selected cell. In a typical table of this nature there would be descriptive row and column headers. To do a lookup on this table you would then look for the intersection of a specific row header and a specific column ...

Column Width Behavior
I use pivot tables alot - and I am working with a pivot table where I've set the column width to 9. When I pivot new information in, the column width automatically adjusts to fit, then I have to go and reset the columns back to 9. Is there anyway to override this "adust to fit" behavior and just let the pivot table fit as much text as it can into the column width I specify? Kirk, Unselect the 'Autoformat Table' option in the Options section of the Pivot Table. HTH, Gary Brown "Kirk P." wrote: > I use pivot tables alot - and I am working with a pi...

"Changed by" Column is empty
Outlook 2003/Exchange 2000 Hi, in my mailbox, the "Changed by" column remains empty throughout all folders. When I change to my Team Mailbox, the "Changed by" column has the desired information. Even when I move a mail from the Team Mailbox to my mailbox, the "Changed by" column remains empty. Is this a bug or I am doing something wrong. Thank you very much. Christoph ...

Printing 2 worksheets to a 2 sided document
Is there any way to print - front to back - 2 seperate worksheets? Turn the paper over and print the second worksheet???? Maybe you can create a worksheet with a picture of both ranges on it: Insert a new worksheet Edit|copy the first range shift-Edit|Paste Picture Link (on that new worksheet) Back for the second range and shift-edit|Paste picture link (right near your first pasted picture link). (insert a nice page break, too) And by pasting a link, you can change the original range and your picture will update right away. (Keep that worksheet as long as you want and print from there???)...

Export #2
Hi, How to export only the perticular group users. Regards Mustafa What sort of information do you need? "Mohammed Mustafa" <mohammed.mustafa@kharafinational.com> wrote in message news:OwKSDO$yGHA.3568@TK2MSFTNGP03.phx.gbl... > Hi, > > How to export only the perticular group users. > > Regards > Mustafa > See "IMI GAL Exporter" - http://www.imibo.com/imidev/Exchange/imige.htm "Mohammed Mustafa" <mohammed.mustafa@kharafinational.com> wrote in message news:OwKSDO$yGHA.3568@TK2MSFTNGP03.phx.gbl... > Hi, > > How ...

solver and macros #2
Before you use the solver within a macro, you must establish a reference to the Solver add-in. With a Visual Basic module active, click References on the Tools menu, and then select the Solver.xla check box under Available References. If Solver.xla doesn't appear under Available References, click Browse and open Solver.xla in the \Office\Library subfolder. Cheers Nick ...

Closing registers #2
1. Is there any way too remove the closing figure when the registers are closed... I don't want my cashiers to see the closing amounts. 2. Can a group of registers be closed out on a selected machine, e.g Reg 1, reg 2, reg 3, reg 4 are all closed on reg 1. Thanks If you don't want cashiers to see closing amounts, lock them out of doing x/z reports and viewing the journal - apply this setting in SO manager - database - cashiers - properties of each cashier - uncheck these boxes. On question 2, out of the box, RMS does not allow that - each register has its own batch that must ...

CRM v1.2 for MSDN?
We are a UNIVERSAL MSDN Subsciber which means we pay about $4000 a year to get the entire Microsoft Library. I have not seen CRM 1.2 in any of the updates which is very strange since I normally see them before any other subscription. Did I miss an update? I also notice on the MSDN downloads section they still only have v1.0 listed. Thanks! CRM 1.2 is only just shipping to partners etc. I would not expect it to appear on MSDN for a couple of months "MEI" <MEI@MEI.COM> wrote in message news:Of0KXo7sDHA.3496@TK2MSFTNGP11.phx.gbl... > We are a UNIVERSAL MSDN Subsciber whic...

hyperlinks #2
When I click on a hyperlink in outlook IE opens and stays blank, but another window opens and asks me to Locate link browser. I have been closing this window, but it reappears on every link. ??????????????? It may help you http://support.microsoft.com/default.aspx?scid=kb;en- us;329912&Product=out >-----Original Message----- >When I click on a hyperlink in outlook IE opens and stays >blank, but another window opens and asks me to Locate link >browser. I have been closing this window, but it reappears >on every link. ??????????????? >. > ...

Freezing Panes #2
How can I freeze two different rows in a single sheet. -- Naidu Pl don't multi-post. Check responses in .misc -- Max Singapore http://savefile.com/projects/236895 Downloads:17,000, Files:358, Subscribers:55 xdemechanik --- "Naidu" <Naidu@discussions.microsoft.com> wrote in message news:967FF8C4-431D-4BAE-9903-ECCDE18157D7@microsoft.com... > How can I freeze two different rows in a single sheet. > > -- > Naidu ...

Deleting Mail #2
This problem seems to be random over our network. We are running Windows Server 2000 with Outlook 2002. Some users are getting a notification that they have mail and when they check, there is nothing in their inbox. The new mail has automatically gone to the deleted items folder. It doesn't do it with all mail for that particular user or do all the users on the network experience this problem. There are no rules set up to do this. Has anyone else have this problem. If you have a suggestion please respond to my e-mail address DKimball@puc.nh.gov Thanks I have Outlook 2002 on...

Pay commissions to 2 salespersons
Is there a way to pay commissions to two different salespersons for one line item? When I try to enter secont salesperson in "Sales Commissions Entry" window I receive a message "The commission amount may not be greater the sale amount distributed to this salesperson" Commissions in GP are calculated a little differently than what we think about. If I have two salespeople and I want to give each a 5% commission on the sale, then I need to give each of them a 10% commission on 50% of the sale. -- Jim "Pay commissions to 2 salespersons" wrote: > Is th...

Hpw to serialize boolean to 0/1 into XML?
I can deserialize my Xml that contains 0/1 to bool, but when I again serialize it, value is true/false. Is there some way to force boolean value to be serialized as 0 and 1, not true or false? Thanks. Rita wrote: > I can deserialize my Xml that contains 0/1 to bool, but when I again > serialize it, value is true/false. Is there some way to force boolean value > to be serialized as 0 and 1, not true or false? I suspect it is not possible, looking at http://www.w3.org/TR/xmlschema-2/#boolean the possible values are 0, 1, true, false but the canonical values are only tr...

hyperlink #2
How do I make a hyperlink without underline? Thanks, Jo refer to http://www.publishermvps.com/Default.aspx?tabid=30 -- David Bartosik - MS MVP for Publisher help: www.davidbartosik.com enter to win Pub 2003: www.davidbartosik.com/giveaway.aspx "jo" <jo@discussions.microsoft.com> wrote in message news:1EA75A54-A237-4090-A4A7-EEEBE9DDA2B7@microsoft.com... > How do I make a hyperlink without underline? > > Thanks, > Jo ...

Time #2
I need to be able to click on a cell and have it be populated with the current HH:MM:SS. I know ctrl+shift+; will do the job but I need it to be even more simple than that for the end users. One way: Put this in the worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) With Range("J1") If Not Intersect(Target, .Cells) Is Nothing Then .Value = Time .NumberFormat = "HH:MM:SS" End If End With End Sub ...

I selected row 1 and row 2 to freeze and it is freezing at row 12
I am selecting row 1 and row 2 to freeze and excel keeps freezing at row 12? Select one row only, if you want 2 rows above where it freezes select row 3, that will leave row1 and 2 unaffected when you scroll down -- Regards, Peo Sjoblom "Lori Brooks" <Lori Brooks@discussions.microsoft.com> wrote in message news:C968CA9B-35A3-427E-993C-D36F3B6D37B7@microsoft.com... >I am selecting row 1 and row 2 to freeze and excel keeps freezing at row >12? Hi, To freeze row 1 & 2 select A3 and then Window|Freeze Panes Mike "Lori Broo...

Outlook and viruses #2
Hi folks, Obviously viruses need to be run, however I have heard of virus infected emails that need to be opened to infect a pc....not the attachment, the email. If this is true then does the mail window which allows one to see an email without opening it pose a risk? I do not open junk email, but it may open in this window. Thanks, Danny Yes, HTML messages can be a risk in older versions of Outlook. One of the first ones that I remember is the KAK worm. This bugger arrived as an innocent HTML message that exploited an Active-X component on the machine to place a file in a pre-d...