How to Merge Information that is on multiple rows into one row

Shermac

New Member
Joined
Mar 29, 2013
Messages
3
I have been looking all over how to do this. I have a spreadsheet that has many contacts (8,000). I have many lines, not all, that have a contact duplicated but each line has a different piece of infomtion:

Example
Company Name Email Phone Fax
ABC company Joe Man jman@abc.com
ABC Company Joe Man 555-456-8974
ABC Company Joe Man 555-456-1212

Like I said not all lines are like this, some only have two :)
What is the best way to merge so all the information for one person is in one row?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Hi Shermac,

Welcome to the forum.

With the example data shown what would result look like?

Regards,
Howard
 

Shermac

New Member
Joined
Mar 29, 2013
Messages
3
I would like it to be all on one row.
ABC Company Joe Mann phone fax email
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
I'm sorry, but as far as I can get you is something like this, where each space you see denotes a cell unto its own.
So the data is in seven cells in a row and not in the order you want.

Rich (BB code):
jman@abc.com 555-456-8974 ABC Company Joe Man 555-456-1212

I ran three macros in sequence on the data as posted to get to this point. First macro transposed each row of data from a column to a single row. Next macro was a text to column to put everything in a single cell in that row and the last macro removed any duplicates.

You could use something on the above like... =C1&" "&D1&" "&E1&" "&F1&" "& etc ....then copy paste special values to get the sequence. A lot of work though.

I would expect further problems if the company name was something like -"W. W. Parker & Sons, Inc", "Dewey, Screwem, Screwem & Howe, LLC" or if the name was "Dr. and Mrs Edward Sawbones".

Regards,
Howard

<tbody>
</tbody>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top