Forum Discussion
Solved
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 diff...
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.
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.