Need help making a macro that will find, copy, and paste a cell with a comma in it

Hello All

Relative noob here and I am stuck on how to do something using visual basic in Excel. I searched the forums and still am coming up with nothing. I have taken a spreasheet and modified it so that it looks something like this:

ColumnA ColumnB Column C
blank name, name1
blank Drug Type
blank XXX Abstract
blank XXX Abstract
blank XXX Oral Presentation
blank XXX Poster
blank XXX Poster
blank XXX Review Article
blank name, name2
blank Drug Type
blank XXX Review Article
blank XXX Review Article
blank name, name3
blank Drug Type
blank XXX Abstract
blank XXX Abstract
blank XXX Abstract
blank XXX Abstract

I would like to be able to use a macro to make the sheet look like this:

ColumnA ColumnB Column C
name, name1 name, name1
name, name1 Drug Type
name, name1 XXX Abstract
name, name1 XXX Abstract
name, name1 XXX Oral Presentation
name, name1 XXX Poster
name, name1 XXX Poster
name, name1 XXX Review Article
name, name2 name, name2
name, name2 Drug Type
name, name2 XXX Review Article
name, name2 XXX Review Article
name, name3 name, name3
name, name3 Drug Type
name, name3 XXX Abstract
name, name3 XXX Abstract
name, name3 XXX Abstract
name, name3 XXX Abstract

the principle problem being that the number of items under each person's name will be different. I thought the solution would be to have the macro search column B for the presence of a comma in one of the cells and then paste the contents of that cell into the corresponding cell in column and copy until it got to the next instance of a comma in column B. Anybody have any thoughts on how to program such a thing?

Make a sample before and after worksheet and upload the workbook.
The before worksheet is the data as is.
The after worksheet is the data as you would like it to appear.

Hi Steven. Thanks for the offer! I have attached an example excel file. The first tab has the raw format, the second tab has what I would like it to look like after the macro has worked its magic. Thanks again.

See: EricC PhD_excel example.xlsm
Run: AddName2ColumnA

Please Login or Register  to view this content.

StevenM you are a god among men, sir!!! That worked like a charm!

If you are satisfied with the solution(s) provided, please mark your thread as Solved. Also, as a new member, you may not know that you can thank someone by adding to the reputation of those who helped you by clicking the small star icon at the lower left corner of the post in which the help was given.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save