Forum Discussion

CM1927's avatar
CM1927
Copper Contributor
Feb 27, 2024
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...
Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Feb 28, 2024

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

     

rprsridhar's avatar
rprsridhar
Brass Contributor
Feb 28, 2024

CM1927 

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.

 

 

 

CM1927's avatar
CM1927
Copper Contributor
Feb 29, 2024
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.
  • rprsridhar's avatar
    rprsridhar
    Brass Contributor
    Feb 29, 2024

    CM1927 

     

    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 !!!

     

Resources