Picking up extra data lines using FILTER

[Emmaly]

Hello All,

I have not used FILTER before and is it great however i have a problem that i can't get my head around.

I am using a small amount of data to try and prove the concept (attached)

I have a database of suppliers. I am using filter formula so the user can filter the trade of supplier.
This then populates a list of all the Plumbers (for example)
I have then got a data validation picking up the values returned by the filter. The user can then choose one of the plumbers and it will populate the name, telephone number email boxes with all the contacts for that supplier.

The problem i have is duplicates.
The database has got a line for each contact within a supplier.
I have used UNIQUE to make the FILTER formula only return one line.

The Directors would like to develop a scoring system for each supplier that will give a snapshot of the performance.
Because the lines returned by the FILTER formula are greyed out I can't use a VLOOKUP to just return one line.
If I use filter I get extra lines of "score" as in the example attached REF Electrics has two contacts in the database, but UNIQUE is giving me unique scores as i can't get it to look at the returned FILTER list.

I need something to just return 1 line the section with conditional formatting as a snapshot of each supplier.

I hope i have explained my goal clearly enough.

Thanks for helping, this is making my brain hurt. It is probably simple but I just can't think how to solve it.

Emma

Supplier sample data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Trade	Account Reference	Name	SupplierRecord.AddressLine1	SupplierRecord.AddressLine2	SupplierRecord.AddressLine3	SupplierRecord.AddressLine4	SupplierRecord.AddressLine5	Supplier Contact	SupplierRecord.Telephone	Email	Returning prices	Competitiveness	T&E Average	Working to programme	Flexibility of working	H&S	Post project	Project Average	price negotitating	Variation pricing	Commercial Average
2	Plumbing	HEATRADI	Heat Radiation Ltd	Belvedere Trading Estate			Taunton	TA1 1BH	Ros Morse	01823 253177	rosmorse@heatradiationltd.co.uk	5	4	4.5	4	5	5	4	4.5	3	5	4
3	Plumbing	HEATRADI	Heat Radiation Ltd	Belvedere Trading Estate			Taunton	TA1 1BH	Master	07123 456789	jamesriste@heatradiationltd.co.uk	5	4	4.5	4	5	5	4	4.5	3	5	4
4	Plumbing	HEATRADI	Heat Radiation Ltd	Belvedere Trading Estate			Taunton	TA1 1BH	James Riste	07123 456789	jamesriste@heatradiationltd.co.uk	5	4	4.5	4	5	5	4	4.5	3	5	4
5	Electrical	REF-ELEC	REF Electrics (Taunton) Ltd	Unit 22	Acorn Business Centre	Livingstone Way	Taunton	TA2 6BD	Karen Barker	01823 282339	Karen@refelectrics.co.uk	4	5	4.5	3	5	2	5	3.75	4	3	3.5
6	Electrical	REF-ELEC	REF Electrics (Taunton) Ltd	Unit 22	Acorn Business Centre	Livingstone Way	Taunton	TA2 6BD	James Takle	07123 123456	james@refelectrics.co.uk	4	5	4.5	3	5	2	5	3.75	4	3	3.5
7	Electrical	MAINSTON	Mainstones Electrical Ltd	2 Mayors barton	Bourton	Gillingham	Dorset	SP8 5BS	Joe Bloggs	07885 625456	Joe@Mainstones.co.uk	3	3	3	5	5	5	5	5	4	3	3.5
8	Electrical	MJD	MJD Mechanical and Electrical Services	Unit 20	Dragon Court	Bristol		BS5 7XX	Peter Parker	0117 907 0252	Peter@MJD.com	4	4	4	4	5	5	3	4.25	5	5	5
9
10
11
12
13				Trade	Electrical
14
15
16
17		Plumbing			Subcontactor Name	T&E Rating	Project Rating	Commercial Rating
18		Electrical			REF Electrics (Taunton) Ltd	4.5	3.75	3.5
19					Mainstones Electrical Ltd	4.5	3.75	3.5
20					MJD Mechanical and Electrical Services	3	5	3.5
21						4	4.25	5
22
23
24
25
26
27
28
29
30				Company	Heat Radiation Ltd
31
32
33					Name	Number	Email
34					Ros Morse	01823 253177	rosmorse@heatradiationltd.co.uk
35					Master	07123 456789	jamesriste@heatradiationltd.co.uk
36					James Riste	07123 456789	jamesriste@heatradiationltd.co.uk
37
38
masterdata

Cell Formulas
Range	Formula
N2:N8,V2:V8	N2	=(L2+M2)/2
S2:S8	S2	=(O2+P2+Q2+R2)/4
E18:E20	E18	=UNIQUE(FILTER(C2:C8,E13=A2:A8))
F18:F21	F18	=FILTER(N2:N8,E13=(A2:A8))
G18:G21	G18	=(FILTER(S2:S8,E13=A2:A8))
H18:H21	H18	=(FILTER(V2:V8,E13=A2:A8))
E34:E36	E34	=FILTER(I2:I8,E30=C2:C8)
F34:F36	F34	=FILTER(J2:J8,E30=C2:C8)
G34:G36	G34	=FILTER(K2:K8,E30=C2:C8)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F18:H21	Cell Value	<3	text	NO
F18:H21	Cell Value	between 4 and 3	text	NO
F18:H21	Cell Value	>4	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E30	List	=$E$18:$E$27
E13:E16	List	=$B$17:$B$18

 

[jasonb75]

Try this one, it works with the sample but I haven't tested it beyond that.

=LET(arr,UNIQUE(FILTER(C2:V8,E13=A2:A8)),INDEX(arr,SEQUENCE(ROWS(arr)),{1,12,17,20}))

 

[Emmaly]

Try this one, it works with the sample but I haven't tested it beyond that.

=LET(arr,UNIQUE(FILTER(C2:V8,E13=A2:A8)),INDEX(arr,SEQUENCE(ROWS(arr)),{1,12,17,20}))

Thanks Jason, unfortunately it is returning duplicate lines. it is so close though!

 

[Fluff]

How about this slight tweak to Jason's formula

=LET(arr,FILTER(C2:V8,E13=A2:A8),UNIQUE(INDEX(arr,SEQUENCE(ROWS(arr)),{1,12,17,20})))

 

[Emmaly]

How about this slight tweak to Jason's formula

=LET(arr,FILTER(C2:V8,E13=A2:A8),UNIQUE(INDEX(arr,SEQUENCE(ROWS(arr)),{1,12,17,20})))

YAY!!!!! good job Fluff, it works. Thank you so much

 

[Fluff]

Glad we could help & thanks for the feedback.