Getting rid of duplicate values in a cell after using concatenate

[outofgum]

I have a large spreadsheet with 2 columns of names. Each cell may contain anywhere from one to 50 names. I combined the columns using concatenate. The problem is that the names in each column were not unique, so I have some dulicate names which now must be removed. The problem is the duplicates are in one cell. The format of the names is as follows:

Lastname1, Firstname1; Lastname2, Firstname2, etc...

First name and last name separated by a comma, names seperated by a semi colon.

Is there anyway to remove the dupes within the cell? It is quite difficult to do by hand. There are hundred of rows. Thanks!

[MarvinP]

Hi outofgum,

We need to talk about having a cell with multiple names in it. It seems to me Excel works better if names are in separate cells. Perhaps you should post an example file and explain what you have and what you are trying to achieve.

That said. If I had this problem, I'd start by splitting out the named based on the semicolon character. Perhaps using import as text using the semicolon as the delimiter character. Then I imagine a transpose all the imported data and a delete duplicates one each individual row. This would remove duplicates and be ready to put it back like you asked.

All said above - we need a sample. Click on "Go Advanced" and then on the Paper Clip Icon above the message area to attach a sample.

[outofgum]

Thanks for the suggestion! What I did was copy the column to a new workbook and use text to colums using the semi colon as the delimiter.

For some reason remove dulicates wouldn't work across rows (only columns?) so I made a conditional formating macro to apply to each row. All this does is highlight which makes it easier to find the dupes. Can conditional formatting delete cell data? I looked but it doesn't seem like it.

Anyone have an idea of where to go from here?