Consolidate cells with multiple names into a single column with one name per cell

[rbtx70]

I receive an excel spreadsheet with single column of names: Up to 5000 rows with 1 - 30 names per cell (names are delineated by commas):

Example
----------
NAME
betty johnson,bart wong,alissa villa,marlee rodriguez,amy luce,isaac pierce,janet gales,david green,pete davidson
david green,demetrius washington,alissa villa,mike harris,tom serret
amy luce,angel cosh,david green,alissa villa
amy luce,
...

Would like to consolidate into one column, multiple rows, one name per cell, with duplicates deleted:

Example
----------
alissa villa
amy luce
annette sanborn
barthomele kramer
betty johnson
...

[abousetta]

[Deleted post]

Misread OP's requirements.

[rbtx70]

A vba macro would be much appreciated

[abousetta]

[Deleted post]

Sorry misread the original post... back soon.

[rbtx70]

No you read it correct but my requirements were not clear. I edited the request to clarify requirements (added multiple rows). My apologies.

[abousetta]

The macro will take names from column A, put each name in column B (each name a separate cell), sort then remove duplicates.

Let me know if this covers your needs.

abousetta

Please Login or Register  to view this content.

[rbtx70]

Final question. How do I mark this thread as solved? By the way, thankyou very much. This was much cleaner and quicker than the code I was previously using. Your assistance is very much appreciated.

[abousetta]

Thanks for the feedback. Glad it's all working now.

To mark the thread as Solved, click on "Thread tools" (at the top of the first post) >> Mark thread as Solved.

Good luck.

abousetta