Home > microsoft excel > Formula to display text from multiple cells from one sheet in another

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

January 19Hits:1
Advertisement

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!

Answers

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.

Related Articles

Copyright (C) 2017 ceus-now.com, All Rights Reserved. webmaster#ceus-now.com 14 q. 0.539 s.