VB to split and repeat rows based on cell values

Hello all,
I am somewhat new to VB and wish to ask advice. I'm attempting to automate a process which is currently done manually. I have a Excel file which contians columns CLIENT_ID NAME PURCHASE_TYPE DATE_ADDED, MATERIAL . In the MATERIAL column, it is possible for multiple material items to be present and separated by commas and spaces. Occasionally, a comma will be the last value present in the column. I have been tasked with finding rows with multiple material occurances, and splitting the occurances into new rows while copying the data which exists in the current row of CLIENT_ID NAME PURCHASE_TYPE DATE_ADDED. I will try and visuallize below...

CLIENT_ID NAME PURCHASE_TYPE DATE_ADDED MATERIAL
1 N1 E 1/1/2010 A, B, C
2 N2 F 1/1/2010 A,
3 N3 G 1/1/2010 B
4 N4 E 1/1/2010 A, B, C
...and so on...

solution should look like...

CLIENT_ID NAME PURCHASE_TYPE DATE_ADDED MATERIAL
1 N1 E 1/1/2010 A
1 N1 E 1/1/2010 B
1 N1 E 1/1/2010 C
2 N2 F 1/1/2010 A
3 N3 G 1/1/2010 B
4 N4 E 1/1/2010 A
4 N4 E 1/1/2010 B
4 N4 E 1/1/2010 C


Thank you in advance for your cooperation.

Hello rgjohnson,

Welcome to the Forum!

The attached workbook has your example data and the macro below added to it. A button on "Sheet2" will run the macro.

Please Login or Register  to view this content.

The macro posted here does this:

Rows to Columns - Part 2

In the code, just change the COL = 2 to COL = 4 if it's column D you want split out. All the other columns will be duplicated. There's a sample workbook on that page, too.

Hello JB,

As your teacher told you in school, please show your work. It is for the benefit of the whole class.