Picking up extra data lines using FILTER

Emmaly

New Member
Joined
Nov 15, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUV
1TradeAccount ReferenceNameSupplierRecord.AddressLine1SupplierRecord.AddressLine2SupplierRecord.AddressLine3SupplierRecord.AddressLine4SupplierRecord.AddressLine5Supplier ContactSupplierRecord.TelephoneEmailReturning pricesCompetitivenessT&E AverageWorking to programmeFlexibility of workingH&SPost projectProject Averageprice negotitatingVariation pricingCommercial Average
2PlumbingHEATRADIHeat Radiation LtdBelvedere Trading EstateTauntonTA1 1BHRos Morse01823 253177rosmorse@heatradiationltd.co.uk544.545544.5354
3PlumbingHEATRADIHeat Radiation LtdBelvedere Trading EstateTauntonTA1 1BHMaster07123 456789jamesriste@heatradiationltd.co.uk544.545544.5354
4PlumbingHEATRADIHeat Radiation LtdBelvedere Trading EstateTauntonTA1 1BHJames Riste07123 456789jamesriste@heatradiationltd.co.uk544.545544.5354
5ElectricalREF-ELECREF Electrics (Taunton) LtdUnit 22Acorn Business CentreLivingstone WayTauntonTA2 6BDKaren Barker01823 282339Karen@refelectrics.co.uk454.535253.75433.5
6ElectricalREF-ELECREF Electrics (Taunton) LtdUnit 22Acorn Business CentreLivingstone WayTauntonTA2 6BDJames Takle07123 123456james@refelectrics.co.uk454.535253.75433.5
7ElectricalMAINSTONMainstones Electrical Ltd2 Mayors bartonBourtonGillinghamDorsetSP8 5BSJoe Bloggs07885 625456Joe@Mainstones.co.uk33355555433.5
8ElectricalMJDMJD Mechanical and Electrical ServicesUnit 20Dragon CourtBristolBS5 7XXPeter Parker0117 907 0252Peter@MJD.com44445534.25555
9
10
11
12
13TradeElectrical
14
15
16
17PlumbingSubcontactor NameT&E RatingProject RatingCommercial Rating
18ElectricalREF Electrics (Taunton) Ltd4.53.753.5
19Mainstones Electrical Ltd4.53.753.5
20MJD Mechanical and Electrical Services353.5
2144.255
22
23
24
25
26
27
28
29
30CompanyHeat Radiation Ltd
31
32
33NameNumberEmail
34Ros Morse01823 253177rosmorse@heatradiationltd.co.uk
35Master07123 456789jamesriste@heatradiationltd.co.uk
36James Riste07123 456789jamesriste@heatradiationltd.co.uk
37
38
masterdata
Cell Formulas
RangeFormula
N2:N8,V2:V8N2=(L2+M2)/2
S2:S8S2=(O2+P2+Q2+R2)/4
E18:E20E18=UNIQUE(FILTER(C2:C8,E13=A2:A8))
F18:F21F18=FILTER(N2:N8,E13=(A2:A8))
G18:G21G18=(FILTER(S2:S8,E13=A2:A8))
H18:H21H18=(FILTER(V2:V8,E13=A2:A8))
E34:E36E34=FILTER(I2:I8,E30=C2:C8)
F34:F36F34=FILTER(J2:J8,E30=C2:C8)
G34:G36G34=FILTER(K2:K8,E30=C2:C8)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F18:H21Cell Value<3textNO
F18:H21Cell Valuebetween 4 and 3textNO
F18:H21Cell Value>4textNO
Cells with Data Validation
CellAllowCriteria
E30List=$E$18:$E$27
E13:E16List=$B$17:$B$18
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Try this one, it works with the sample but I haven't tested it beyond that.
Excel Formula:
=LET(arr,UNIQUE(FILTER(C2:V8,E13=A2:A8)),INDEX(arr,SEQUENCE(ROWS(arr)),{1,12,17,20}))
 
Upvote 0
Try this one, it works with the sample but I haven't tested it beyond that.
Excel Formula:
=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!
ScreenHunter 10.png
 
Upvote 0
How about this slight tweak to Jason's formula
Excel Formula:
=LET(arr,FILTER(C2:V8,E13=A2:A8),UNIQUE(INDEX(arr,SEQUENCE(ROWS(arr)),{1,12,17,20})))
 
Upvote 0
Solution
How about this slight tweak to Jason's formula
Excel 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 :)
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top