Forum Discussion
Help Needed with Unique Filter Formula
Hello Excel Forum,
I am having problems creating a unique filter formula the uses multiple criteria.
My data is set up in following two columns:
- Job Number
- Product Code
Up to 5 different product codes can be associated with the same job number. I would like to create a formula that filters the job numbers that only contain the product codes selected.
Let's say the Product Codes I want to select are:
- BIR155
- REAS339
- TAS043
There are only 3 job numbers that contain only these product codes.
- Some job numbers contain only 2 out of the three codes.
- Others contain the three codes I selected plus two other codes.
I would like the formula to return the ojob numbers with only the 3 codes I selected.
A screenshot of my data is provided below.
The formula I tried to use is:
=UNIQUE(FILTER($A$3:$A$40,(COUNTIFS(B3:B40,D4)*COUNTIFS(B3:B40,D5)*COUNTIFS(B3:B40,D6))))
This returns every job number that contains any one or more of the codes.
Any help you can provide will be greatly appreciated.
Thank you.
CM1927 sorry we didn't understand what you needed. If i understand correctly now, you want to only return items that ONLY have those criteria items and ALL of those criteria items. this is a little harder and the formula a bit more complicated but with office 365 this should work:
=LET(names,A4:A19,codes,B4:B19,criteria,D4:D6, UNIQUE(FILTER(names,BYROW(names,LAMBDA(a,IFERROR(PRODUCT(--(SORT(UNIQUE(FILTER(codes,names=a)))=SORT(criteria))),0))),"")))
12 Replies
This formula is intended as a full-on exploration of newly available functionality.
= LET( BooleanArray, CODE=TOROW(REQUIRED,3), groupedByJob, GROUPBY(JOB, BooleanArray, OR,,0), distinctJobs, TAKE(groupedByJob,,1), matchingCrit, BYROW(DROP(groupedByJob,,1), AND), FILTER(distinctJobs, matchingCrit) )1. The first line compares every code with each of the required codes.
2. Next, they are grouped by job to see whether the required codes are to be found in any instance of the job (see image).
3 & 4. The jobs are simply separated to there own array but the criteria for each job are combined, requiring that all (three) are satisfied.
5. Finally the distinct jobs are filtered by the combined criterion to give the result.
I think this solution does what you are asking for.
Consciously though I have avoided using let, lambda etc.,
I have added a helper column to simplify things.
I have also attached the excel file. Your feedback will be appreciated.
- Dear rprsridhar,
Thank you very much for providing this info to me. It was a very clever approach.
I went with another solution because I couldn't figure out how to adjust your formula to calculate correctly with less than 3 selections. I should have mentioned sometimes only 1 or 2 product codes are associated with job numbers.
Thanks again for your time. I really appreciate it.I am glad that you got a perfect solution.
However this solution also works with 0 1 or 2 product codes also.
Anyways the reason I try to contribute is for my own practice.
I am getting older and I am forgetting things.
This helps me to find solutions to variety of problems
I am able to brush my knowledge.
So absolutely no issues.
Cheers !!!
Job number 2103 and 2105 has exactly the same product codes. Yet you have selected 2103 but not 2105. That makes your question very ambiguous.
My mistake. Let's try this again. A new screenshot is pasted below.
I'm looking for a Unique Filter formula that returns only the job numbers that contains the product codes selected in cells D12:D14.
In this example, only one job number contains all three product codes selected (BIR155, REA339, and TAS043).
Any help you can provide will be greatly appreciated. Thanks.
CM1927 sorry we didn't understand what you needed. If i understand correctly now, you want to only return items that ONLY have those criteria items and ALL of those criteria items. this is a little harder and the formula a bit more complicated but with office 365 this should work:
=LET(names,A4:A19,codes,B4:B19,criteria,D4:D6, UNIQUE(FILTER(names,BYROW(names,LAMBDA(a,IFERROR(PRODUCT(--(SORT(UNIQUE(FILTER(codes,names=a)))=SORT(criteria))),0))),"")))
- Thanks anyway Harun24HR,
The formula you provided returned all of the job numbers even if they contained only one or two of the three product codes that were selected. I need a formula that returns only the Job numbers that were selected.
In the example you provided, the formula should have returned only job number "5poly-2006" because that was the only one that contains all three product codes (BIR155, REA339, and TAS043).
The other job numbers contained FAM142 which was not one of the selected product codes.
Thanks anyway for taking a crack at it. I appreciate your time.
Please let me know if you can think of another option that will yield the desired results.
- so basically when you use product (*) that means AND but in this case I believe you want OR so try using plus (+). I would also just use an equality instead of COUNTIF so maybe something like this:
=UNIQUE(FILTER($A$3:$A$40,(B3:B40=D4)+(B3:B40=D5)+(B3:B40+D6)))m_tarler ,
Thanks anyway but the formula you provided returned all of the job numbers that contained any combination of the product codes I selected. I need the formula to return only the job numbers that contain just the three coded that were selected.
Oh well, back to the drawing board...
Thanks again for your help. I appreciate your time.
