+ Reply to Thread
Results 1 to 8 of 8

How to merge to columns into one, putting them into every other cell?

  1. 04-27-2012, 03:11 AM #1
    NTNU-trykk
    NTNU-trykk is offline
    Registered User
    Join Date
    04-17-2012
    Location
    Norway
    MS-Off Ver
    Excel 2007
    Posts
    12

    Question How to merge to columns into one, putting them into every other cell?

    Hi there

    We have a excel file with two columns: Usernames (A) and password (B). We need to merge/intertwine these columns into one, placing all the information into every other cell. Example:

    -NOW-

    Username Password
    ABC 123
    ABC 123
    ABC 123



    -WE NEED-

    User/password *now blank*
    ABC
    123
    ABC
    123
    ABC
    123
    ABC
    123




    The reason we need it like this is simply an old way to go about it as the client wants one sticker with the username and one with the password and they need to be after each other so they know what password goes to which username when they mail it.

    In advance, thanks for your help!
    Reply With QuoteRegister To Reply
  2. 04-27-2012, 03:18 AM #2
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: How to merge to columns into one, putting them into every other cell?

    Try this code 
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose View | Macros
    Select a macro in the list, and click the Run button.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  3. 04-27-2012, 03:27 AM #3
    Mallycat
    Mallycat is offline
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: How to merge to columns into one, putting them into every other cell?

    Full marks to you NTNU-trykk for an excellent "model problem" post. Great description in the title, great example clearly outlined with before and after, and even an explanation as to why. If only all requests for help were so well communicated.
    Reply With QuoteRegister To Reply
  4. 04-27-2012, 05:13 AM #4
    Marcol
    Marcol is offline
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How to merge to columns into one, putting them into every other cell?

    A possibility without using VBa
    Wrap this in an IF() statement if you want to avoid the formula returning zeros
    Please Login or Register  to view this content.
    so ....
    In D2
    Please Login or Register  to view this content.
    Drag/Fill Down as required.

    Then Copy > Paste Special > Values to your desired location.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
    Reply With QuoteRegister To Reply
  5. 04-27-2012, 12:53 PM #5
    icestationzbra
    icestationzbra is offline
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: How to merge to columns into one, putting them into every other cell?

    a couple of formulaic methods...
    Attached Files Attached Files
    Last edited by icestationzbra; 04-27-2012 at 10:21 PM. Reason: new method added
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -
    Reply With QuoteRegister To Reply
  6. 04-27-2012, 01:11 PM #6
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: How to merge to columns into one, putting them into every other cell?

    or this formula Assuming data in A2:B15
    In C2 dragged down

    =INDEX($A$2:$B$15,INT((ROW(A1)+1)/2), MOD(ROW(A2),2)+1)
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  7. 04-27-2012, 10:20 PM #7
    icestationzbra
    icestationzbra is offline
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: How to merge to columns into one, putting them into every other cell?

    akin to CB's, another method that i found some expert using:

    if your data are in A2:B4, start in any other column at any row:

    =INDEX($A$2:$B$4, CEILING(ROW(A1)/2,1), MOD((ROW(A2)),2)+1)
    Last edited by icestationzbra; 04-29-2012 at 09:37 PM.
    Reply With QuoteRegister To Reply
  8. 04-28-2012, 02:11 AM #8
    Marcol
    Marcol is offline
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How to merge to columns into one, putting them into every other cell?

    Or maybe just ...
    Please Login or Register  to view this content.
    And hence to avoid zeros or errors when the table runs out
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1