Excel help - much appreciated!

[phxomega]

I have a question - I have a data set that currently looks like this

Product A | Sales person a, sales person b
Product B | Sales person a, sales person c
Product C | Sales person b, sales person d, sales person e

I need a report that looks like this (reverse of what I have above)

Sales person a | product A, Product B
Sales person b | product A, Product C
Sales person c | product B
Sales person d | Product C
Sales person e | Product C

What would be the best approach to do this - was thinking about pivot table and text to columns, but I cannot find a way to get there easily. The actual data set is actually in the 1000s. Thank you for a prompt response.

 

[igold]

Hi phxomega,

Welcome to the MrExcel Forum.

I too have a question - how many columns does your current data set contain and how many columns do you want your resultant data set to be in?

 

[phxomega]

The number of columns are not many (2), but the tricky part is that I have cells that contain the data separated by 20 commas - 1 product can be mapped to 20 different sales person.

 

[igold]

Not 100% sure on what your data could look like. Could you show it graphically with column letters and what data that would fall in the columns. Same with how you would like the output to look. Or at least provide a written description.