Forum Discussion

Rodney2485's avatar
Rodney2485
Brass Contributor
Jan 13, 2024
Solved

Unique List, excluding certain criteria

  I need to make a New list of Pickticket's that exclude the List of Customers in Column "C" =UNIQUE(FILTER(A:A,(A:A<>"")*(B:B<>"OZARK"))) Doesn't seem to be working   Pickticket Customer Ex...
excel
  • OliverScheurich's avatar
    OliverScheurich
    Jan 13, 2024

    Rodney2485 

    =UNIQUE(FILTER(A2:B1000,(A2:A1000<>"")*BYROW(B2:B1000,LAMBDA(x,SUM(N(ISNUMBER(SEARCH(C2:C5,x))))=0))))

     

    With Office 365 or Excel for the web you can apply this formula. For better performance i'd reference e.g. A2:B1000 instead of A:A since A:A refers to 1048576 rows.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 13, 2024

Rodney2485 

This is pretty similar to OliverScheurich 's solution but allows me to try our an 'eta reduced Lambda function' now that I finally have it!

= LET(
    isPresent, ISNUMBER(SEARCH(TOROW(Exclude), Table1[Customer])),
    excluded,  BYROW(isPresent, OR),
    FILTER(Table1, NOT(excluded))
  )

If eta reduction is not available, a Lambda function ORλ needs to be defined to be used in place of OR

"ORλ"
= LAMBDA(x, OR(x))

 

 

Resources