1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Help Me Consolidate Excel Column Data Into One Column - I Don't Know How

Discussion in 'General Programming Chat' started by crazyflx, Dec 18, 2010.

  1. crazyflx

    crazyflx Elite Member

    Joined:
    Nov 9, 2009
    Messages:
    1,674
    Likes Received:
    4,825
    Location:
    http://CRAZYFLX.COM
    Home Page:
    I'm going to give a very simple example of what I need done:

    Column A - Column B - Column C - Column D
    ________|________|_________|________|
    | John___| __Lisa__ | Steve____ | Kate ___|


    I need all the data from each column moved into one column like this:

    Column A |
    ________|
    | John __ |
    | Lisa ___|
    | Steve __|
    | Kate ___|

    Each column is going to have multiple rows of data (thousands of entries).

    Does anybody know how to make Excel do that for me?
     
    Last edited: Dec 18, 2010
  2. tb0n3

    tb0n3 Newbie

    Joined:
    Nov 28, 2010
    Messages:
    45
    Likes Received:
    20
    Go to Column D and then type the equal sign, then click on the cell in Column A, then type & " " &. then click on column B and then type & " " &
    The trick is to first type the = equal sign to let Excel know you are doing a formula, when you click in each field, it automatically inserts the reference to that cell in Excel. The & " " & is basically you putting a space in between the names

    Once you have the first row done, you can go to the column D cell you created the formula, mouse over the bottom right corner of the cell until you see the + mark, left click and drag it down for it to copy the formula down to all the columns. Hope this makes sense.
     
  3. tb0n3

    tb0n3 Newbie

    Joined:
    Nov 28, 2010
    Messages:
    45
    Likes Received:
    20
    sorry, looks like I misread what you are trying to do
    1)highlight and copy the data with ctrl-c or right click and select copy
    2)click the cell where you want to copy the data
    3)right-click and select "paste special"
    4)at the bottom right, there is a transpose checkbox
     
  4. crazyflx

    crazyflx Elite Member

    Joined:
    Nov 9, 2009
    Messages:
    1,674
    Likes Received:
    4,825
    Location:
    http://CRAZYFLX.COM
    Home Page:
    I appreciate you taking the time to explain that, but it's not quite what I'm looking for.

    Each row has over 60,000 entries. I just need all 60,000 entries in each of the 4 columns to be put into one single column.

    But not like this:

    Column A
    John Kate Steve Paul

    But like this:
    John
    Kate
    Steve
    Paul
     
  5. crazyflx

    crazyflx Elite Member

    Joined:
    Nov 9, 2009
    Messages:
    1,674
    Likes Received:
    4,825
    Location:
    http://CRAZYFLX.COM
    Home Page:
    Yes, that's how I have been doing it (copy & paste).

    I was looking to see if there was a faster way. Sometimes I'll have 26 columns, with each column having 20 to 50 thousand entries.

    I was looking to see if there was a more efficient way of doing it.
     
  6. armyf9

    armyf9 Newbie

    Joined:
    Oct 17, 2009
    Messages:
    1
    Likes Received:
    0
    The way i'd do it is: i'd export the file as a CSV, open in notepad2 (or word) replace the commas with \r\n (newline character), save as csv... open it again in excel.
     
  7. dundomaroje

    dundomaroje Newbie

    Joined:
    Dec 17, 2010
    Messages:
    4
    Likes Received:
    1
    hi.. I wrote similar program for personal use some time ago.. but it only works on 64 bit systems.. I will share it with you if you want! :)