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))),"")))
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)))
=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.