Formula to display text from multiple cells from one sheet in another

Question

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!

Answer 1

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.