Many to many relationship: convert one row to m×n rows [closed]

Question

I'm not sure how to explain this, but I have two columns that have multiple data words in each cell per column (Notional Data) that need to be combined, but separately.

             Column A                           Column B                Column C
Row 1   Apple; Orange; Pear; Watermelon    Seedless; Sour; Sweet; Red    CAT 1
Row 2   Grape; Watermelon; Kiwi            Green; Sour; Large; Red       CAT 2
                   ︙
...etc...

I have over 200 rows like this, and I need to convert them to look like this:

                Column A
Row 1   Apple;Seedless;CAT 1
Row 2   Apple;Sour;CAT 1
Row 3   Apple;Sweet;CAT 1
Row 4   Apple;Red;CAT 1
Row 5   Orange;Seedless;CAT1
Row 6   Orange;Sour;CAT 1
Row 7   Orange;Sweet;CAT 1
Row 8   Orange;Red;CAT 1
Row 9   Pear;Seedless;CAT1
                ︙
....etc...

After all possible combinations of source row 1 have been expanded, then move on to source row 2.

Answer 1

You need to first of all separate the values, then you can recombine the ones you want.

Try using using the text to columns tool.

http://office.microsoft.com/en-gb/excel-help/split-names-by-using-the-convert-text-to-columns-wizard-HA010102340.aspx

and specifying the delimeter as ;

Then use concantenate to assemble the parts you want

http://office.microsoft.com/en-gb/excel-help/concatenate-function-HA102753085.aspx?CTT=1