+ Reply to Thread
Results 1 to 5 of 5

How to move data (A1 + B1, A1 + C1, A1 + D1... A2 + B2, A2 + C2, A2 + D2...) to new sheet

  1. #1
    Registered User
    Join Date
    04-22-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    5

    How to move data (A1 + B1, A1 + C1, A1 + D1... A2 + B2, A2 + C2, A2 + D2...) to new sheet

    My data is laid out as below (except dozens more columns and thousands of rows):

    A B C D
    Tom Black Sunday Dallas
    Dan Green Monday Austin
    Bob White Friday Houston

    I need it to be presented as such in a new sheet:

    A B
    Tom Black
    Tom Sunday
    Tom Dallas
    Dan Green
    Dan Monday
    Dan Austin
    Bob White
    Bob Friday
    Bob Houston

    Is there any sort of formula to do this or any recommended procedure? I have around 100 columns and 2,000 rows so the manual ways I can think to do this would be extremely time consuming and error prone.

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: How to move data (A1 + B1, A1 + C1, A1 + D1... A2 + B2, A2 + C2, A2 + D2...) to new sh

    simplest way of doing i can think of considering only dozen of columns.....(unless you feel compelled to write a macro)

    insert column in between each second name....
    put in = $A1 into first cell and fill down

    Data will look like this in seconds

    ABCDEF
    Tom Black Tom Sunday Tom Dallas
    Dan Green Dan Monday Dan Austin
    Bob White Bob Friday Bob Houston

    hard code it from here and then its its simple matter of cut/paste and sort

    would not take very long to do with only couple of thousand names
    5 minutes max if your proficient at excel navigation
    Last edited by humdingaling; 04-22-2013 at 01:54 AM.

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to move data (A1 + B1, A1 + C1, A1 + D1... A2 + B2, A2 + C2, A2 + D2...) to new sh

    Try this...

    Please Login or Register  to view this content.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Registered User
    Join Date
    04-22-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How to move data (A1 + B1, A1 + C1, A1 + D1... A2 + B2, A2 + C2, A2 + D2...) to new sh

    Sixthsense, you are a god among mortals!! :-)

    Quote Originally Posted by :) Sixthsense :) View Post
    Try this...
    Last edited by magus474; 05-01-2013 at 04:23 PM.

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to move data (A1 + B1, A1 + C1, A1 + D1... A2 + B2, A2 + C2, A2 + D2...) to new sh

    Glad it helps you and thanks for the feedback

    Please remove my quotes from Post #4 since it's unnecessarily capturing the screen space and quoting the whole post is against forum rules too...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1