Split cell data into multiple new rows and copy other column values

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I've edited your post this time and added the code tags.

Could you update your example file to attach another sheet to show how this data should look when split out. I'm not sure if you want the formulas that are in rows 2,3 and 4 to go with the new data, or the rest of the data from the actioned row. And where should the expanded data go? start in row 2, or in the newly created output rows.

rylo

Hi Rylo, Sorry about that will make sure I do that in future.

Here is a revised attachment with worksheet showing how I would ideally like this working.

If row 2 colum c has multiple values lets say (three), I want 3 new rows added underneath for each value i.e. one for Sallie, richard and nais, and then the remaining values within row 2 (A,B,D,E,F,G,H) copied for each new row added.

Please also note that it will not always be C5 with the multiple values separated by alt enter it could be all of the rows that have multiple values in colum c.

Hope this makes sense.

Sal.

Hi

See how this goes.

Please Login or Register  to view this content.

rylo

Hi, that works great however how once it has created new rows and pasted values, is there a way to delete the original row with the values in one cell? Also if I click out of speadsheet and back again, it keeps repeating the action. Is there a way to stop it?

Hi

1) This will delete the original row.

Please Login or Register  to view this content.

2) Not really - that is why it is a sheet activate macro. However, once it has processed the data, it will create, then delete a single row, so it will end up as though it has not actioned. Would that suffice?

rylo

Hi Rylo,

looks great - just wanted to check that this creates new rows for data i.e. it should not copy down over existing rows with data, and it will delete the original row. Also if this appears on three rows say 5, 9 and 11 the same actions will apply?

Thanks again for your help on this.

Sal.

Hi

1) New Row - Yes
2) Delete original row - yes
3) Multiple rows - yes will action all rows.

rylo

Thanks so much - appreciate your help.

All the best
Sal.