Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

share|improve this question
    
A pivot table would work, or a macro could do this. You could also try using an array formula –  Raystafarian Jan 20 at 11:20
add comment

1 Answer

A pivot table can do this, albeit a bit clunkily. Select your data and column headers (only one row for headers), Insert | Pivot table. Drag each element into the "Row headers" area. You should now see something resembling your desired result. It will be messy, but can be cleaned up by turning off subtotals on each of the header fields. Last, replicate the sheet with the pivot table and filter the Sales Rep field.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.