Script to Copy from Column B and past in new Row underneath Column A

I have a large spreadsheet full of data where a list of names are in single cells of data and I would like all of the names to have their own row so that I can sort the data. I was able to deliminate the names to columns, there is up to 5 columns of names per row now. Some have only 1 and some have 2 or 3 and some have 5.

I would like to use a macro that would check columns B-F in a row and if it contains additional names in those rows, to cut (or copy) them and insert new row underneath and place the names in column A in the new rows so that all names have their own rows of data. So if Row 2 had text in all five columns. The macro would insert 5 rows underneath row 2 and paste the data from column B to row 3 and from column C to row 4 ... and so on. Then the formula would go on to the next row of data and check to do the same. There would be a caveat where it would simply skip over and go to the next if there is no data in rows B-F or if there is only 3 columns of data, I would like it to do just the three columns that have names and then go to the next (not insert 5 rows for each line item when there is blank data (although I suppose I can go back and filter them out). This macro would have to loop until all rows are complete.

Any suggestions on how to write this? Thank you!

No need to split the data into separate columns, you can use

Please Login or Register  to view this content.

This assume the delimiter is a comma, if not change the comma in red to suit

Originally Posted by Fluff13

No need to split the data into separate columns, you can use

Please Login or Register  to view this content.

This assume the delimiter is a comma, if not change the comma in red to suit

Except I have large amounts of data in columns G through X and I don't want those to be touched. I want to copy them down to the added rows later (or if the formula can do it, have the formula do it. But they would go in columns G through X in the added rows.

Possibly...

Please Login or Register  to view this content.

How about

Please Login or Register  to view this content.

Originally Posted by Fluff13

How about

Please Login or Register  to view this content.

I think you are on the right track with this, the delimiter is actually spaces, however and the macro is not picking that up if I change "," to " "

I am trying to upload an example file but the forum is not letting me.

There are thousands of rows with a column that has names like this:

Jim James
Jo Joe Johanna Hanna
Dave David Davidson

I want to make it:

Jim
James
Jo
Joe
Johanna
Hanna
Dave
David
Davidson

Meanwhile not losing the unique data in the columns to the right and copying them down. The macros are not recognizing the random spaces as the delimiter which is why I am thinking I have to go text-to-columns first? Or I can clean up the cells and make a comma delimiter.

This works for me with spaces

Please Login or Register  to view this content.

If that doesn't work
To upload a workbook

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Attached is an example of what I am trying to do. sorry about that, thank you!

Thanks for the file, how about

Please Login or Register  to view this content.

Originally Posted by Fluff13

Thanks for the file, how about

Please Login or Register  to view this content.

This works! Thank you very much for your help! Sorry I didn't lead with my excel file earlier!

Glad to help & thanks for the feedback