Text to columns - new row - copy/paste original row

Hello again Excel guru community,

Unfortunately again I have encountered a road-block in my skill-set that is blocking me from developing something in Excel to make my (and my colleagues) days easier!

We have a data list of 10,000 or so rows and 24 columns. This list is a a central reference point for much of what we work with but it changes a lot, meaning as things change/update we must refresh our own lists that are linked to it. The main thign we reference with our work is contained in column A and most often we work with vlookups to search for the data we need.

The issue I have is that our vlookups often return errors as the 'food' in Column A is often 'grouped' into 1 cell if it meets the same criteria as other foods. In the attached file I have provided an example of this in cell A11 of the 'Original' tab.
My goal is to be able to lookup what food is in column A and ensure that and food that is 'grouped' into the same cell does not return an error. I imagine the quickest way to do this is create a macro that finds any food in column A that is grouped and then takes each food and cuts/pastes it into it's own row with the rest of the data in the original row copy/pasted to the new rows. (I am hoping the attached sheets shows this)

Does anyone have/know a piece of VBA code I can use in my macro to achieve this??

Luckily, the comma character (,) is the character that will only appear in column A if that cell is 'grouping' several foods.

Perhaps the method I am have suggested is not the most efficient at achieving what I wish so I am also open to the vast knowledge of you guys!

Any help at all you could offer would be massively appreciated.

I know you asked for a VBA solution.. I'm sure someone will find the time to build you something. But I also wanted to give you a non-vba formula that might come in handy in this case or in simular situations. in a vlookup you can also lookup partial matches with wildcards.. so with the formula below you can find Broccolli in the grouped string

Formula:

Please Login or Register  to view this content.

but it also works if the value to find is in a cell

Formula:

Please Login or Register  to view this content.

maybe not the perfect solution, but it might be a nice addition to your skill-set or toolkit to have available..
see attachment file for testing

That is indeed a nice little trick to have, many thanks Roel