Separate Customer List by Salespersons

[mba_110]

Hi everyone,

I did try Vlookup but its not working, since there is 5 columns as reference for lookup value to select the customer for salesperson it is difficult for me to select which formula can work perfect.

This is the sheet look like for each salesperson, it should have formula in all columns to show their customer list and its information list on columns. If this salesperson's name appeared in main datasheet column E to I then he should have that customer in his list with all details in this sheet.

All Customers List.xlsx
	B	C	D	E
5	CUSTOMER LIST		Salesperson	Sorat Balouch
6
7	S.NO	CUSTOMER NAME	CITY	PAYMENT TERMS
8
9	1
10	2
11	3
12	4
13	5
14	6
15	7
16	8
Sorat

This is Main Datasheet holding all customer data, here you will see we have multiple salesperson for each customers by the name Class 1, Class 2, Class 3, Class 4, Class 5.

All Customers List.xlsx
	A	B	C	D	E	F	G	H	I
7	S.NO	CUSTOMER NAME	CITY	PAYMENT TERMS	CLASS - 1	CLASS - 2	CLASS - 3	CLASS - 4	CLASS - 5
8
9	1	11/11 Aesthetic Clinic	Dubai		Taymaa Bakir	Sorat Balouch	Fillers
10	2	4 U Medical Center - Sole Proprietorship L.L.C	Al Ain		Hashem Birini
11	3	7D Medical Center LLC - Sole Proprietorship LLC	Abu Dhabi		Taymaa Bakir
12	4	A G Care Medical Center FZCO	Dubai		Taymaa Bakir
13	5	A H T Aesthetic Medical Center	Dubai		Taymaa Bakir
14	6	Abeer Al Noor Poly Clinic L.L.C	Dubai		Sorat Balouch
15	7	Acacia Advanced Medical Center LLC	Abu Dhabi		Hashem Birini
16	8	Acacia Medical Center	Abu Dhabi		Hashem Birini
17	9	ACG-NSC	Dubai		Office	Fillers
18	10	Adam & Eve Specialized Medical Centre LLC -Branch	Abu Dhabi		Hashem Birini	Tony Othman
19	11	AdvanceMed Aesthetic Clinic	Dubai		Taymaa Bakir	Sorat Balouch
Summary

I want to create sperate sheet for each salesperson with his customer list, no matter any customer appear twice in multiple salesperson's list, the most important is that salesperson should have customers that his name is mentioned in main Datasheet's any of class column from 1 to 5.

Any help will be much appreciated.

 

[Eric W]

Try:

Book1
	B	C	D	E
5	CUSTOMER LIST		Salesperson	Sorat Balouch
6
7	S.NO	CUSTOMER NAME	CITY	PAYMENT TERMS
8
9	1	11/11 Aesthetic Clinic	Dubai	0
10	6	Abeer Al Noor Poly Clinic L.L.C	Dubai	0
11	11	AdvanceMed Aesthetic Clinic	Dubai	0
Sorat

Cell Formulas
Range	Formula
B9:E11	B9	=FILTER(Summary!A9:D19,MMULT(--(Summary!E9:I19=E5),{1;1;1;1;1}))
Dynamic array formulas.

Make sure that the spelling of the salesperson's names all match, including any spaces. I needed to take some spaces out so that it would work.

 

[mba_110]

Try:

Book1
	B	C	D	E
5	CUSTOMER LIST		Salesperson	Sorat Balouch
6
7	S.NO	CUSTOMER NAME	CITY	PAYMENT TERMS
8
9	1	11/11 Aesthetic Clinic	Dubai	0
10	6	Abeer Al Noor Poly Clinic L.L.C	Dubai	0
11	11	AdvanceMed Aesthetic Clinic	Dubai	0
Sorat

Cell Formulas
Range	Formula
B9:E11	B9	=FILTER(Summary!A9:D19,MMULT(--(Summary!E9:I19=E5),{1;1;1;1;1}))
Dynamic array formulas.

Make sure that the spelling of the salesperson's names all match, including any spaces. I needed to take some spaces out so that it would work.

Thank you very much for help.