Forum Discussion

Rodney2485's avatar
Rodney2485
Copper 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

 

PickticketCustomerExclude ListNew List
5000103080MOBILE PARTS INC.OZARK 
5000103094MOBILE PARTS INC.REILLY 
5000103439MOBILE PARTS INC.O'REILLY 
5000103467MOBILE PARTS INC.MOBILE 
5000104211JOBBERS AUTOMOTIVE WAREHOUSE INC  
5000104236CENTRAL DIESEL INC  
5000104237INTERCHANGE PARTS DIST  
5000104285PARKS AUTO PARTS INC.  
5000104318KEYSTONE AUTOMOTIVE OPERATIONS  
5000104430REGIONAL AUTOMOTIVE WAREHOUSE  
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.

2 Replies

Sort By
  • 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))

     

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    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.

Resources