Help with Excel macro/VBA converting CSV entries into multiple rows

Hi folks - I have a large spreadsheet (over 100,000 rows) with 10 columns of data. Two of the columns have comma separate value entries. I need a macro (or series of macros) that can automatically duplicate rows of data with only a single entry for each such comma separated value.

so today I have: John | Smith | Virginia | Apples, Bananas, Grapes, Mangoes

And I want:

John | Smith | Virginia | Apples
John | Smith | Virginia | Bananas
John | Smith | Virginia | Grapes
John | Smith | Virginia | Mangoes

I need the macro to be "smart enough" to only create duplicate rows for the number of entries in the CSV cell. So, in my example, I had 4 fruit names. If I had 17 fruit names, I'd want 17 rows, each with a single instance of each fruit. If there are two identical fruit names, that's okay - I can live with two duplicate rows of the same exact fruit name.

Thoughts on how to do this??

AirBeast,

can you possibly post a sample spreadsheet to ensure we styart on the right track, doesnt need many rows. Can you also include the expected results when both of the csv cols have multiple entries.

Do you expect the data to be in the current tab or can you live with a new output tab?

jamc

Sure. Attaching a file with Before and After. Sure, no problem on having the output on a second sheet.

airbeast.xlsm

AirBeast,

Try this.

I built it using the example data from your original post and therefore it is looking for the CSV data in Col D. In the absence of any example of the whole 10 cols I simply fudged up the data so that you can see the correct output.

You (we) might have to look at speed if your input file is large as you say. First thing is to get it working correctly then sort out speed if required (my guess based on a similar process on 50,000 rows taking 1.15 minutes, is that it may take a 2 - 3 minutes to process 100,000 rows

jmac