0
I have a list of sales reps and the names of the customers they've sold product to, on one sheet. I have tried VLOOKUP, but it will only return one set of text from the cells. I would like to know, please, if there is another formula/function that can reference the name of the sales rep to display the text contents multiple cells containing the customer's names on separate sheets so that: On Sheet 1:
Customer Customer
Sales Rep 1st Name Last Name
Bob Smith Randy Quaid
Bob Smith Tom Cruise
Bob Smith Sarah Parker
Bill Jones James Franco
Bill Jones Clint Eastwood
Jane Doe Brad Pitt
Jane Doe Bruce Willis
And then, using the formula/function on Sheet 2:
Customer Customer
Sales Rep 1st Name Last Name
Bob Smith Randy Quaid
Tom Cruise
Sarah Parker
...on Sheet 3:
Customer Customer
Sales Rep 1st Name Last Name
Bill Jones James Franco
Clint Eastwood
...on Sheet 4:
Customer Customer
Sales Rep 1st Name Last Name
Jane Doe Brad Pitt
Bruce Willis
As I stated, when I used VLOOKUP, it only returns ONE of the customer's names, not all 2 or 3, so I am at a standstill. I apologize if there is a basic answer to this that I am over looking, and I appreciate all the help I can get. Thank you!
A pivot table would work, or a macro could do this. You could also try using an array formula – Raystafarian – 2014-01-20T11:20:46.930