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

[crazyflx]

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?

 

[tb0n3]

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.

 

[tb0n3]

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

 

[crazyflx]

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.

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

 

[crazyflx]

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

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.

 

[armyf9]

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.

 

[dundomaroje]

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!