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

crazyflx

crazyflx

Elite Member
Joined
Nov 9, 2009
Messages
1,623
Reaction score
4,870
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:
Advertise on BHW
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.
 
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
 
tb0n3 said:
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.
Click to expand...

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
 
tb0n3 said:
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
Click to expand...

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.
 
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.
 
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! :)
 
Advertise on BHW
You must log in or register to reply here.
Sign up now!

Main Menu

Home Main Forum List Black Hat SEO White Hat SEO BHW Newbie Guide Blogging Black Hat Tools Social Media Downloads

Marketplace

Account Selling Content / Copywriting EBooks / Methods Hosting Misc Proxies For Sale SEO - Link building SEO - Packages Social Media Social Media - Panels Web Design More

Making Money

Affiliate Programs Hire a Freelancer IM Journeys Making Money Pay Per Click Site Flipping Want to Buy

BlackHat World

Dispute Resolution Forum Suggestions & Feedback Introductions Lounge
Back
Top