SOLVED

# Unique List, excluding certain criteria

Copper Contributor

# 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 Exclude List New List 5000103080 MOBILE PARTS INC. OZARK 5000103094 MOBILE PARTS INC. REILLY 5000103439 MOBILE PARTS INC. O'REILLY 5000103467 MOBILE PARTS INC. MOBILE 5000104211 JOBBERS AUTOMOTIVE WAREHOUSE INC 5000104236 CENTRAL DIESEL INC 5000104237 INTERCHANGE PARTS DIST 5000104285 PARKS AUTO PARTS INC. 5000104318 KEYSTONE AUTOMOTIVE OPERATIONS 5000104430 REGIONAL AUTOMOTIVE WAREHOUSE
2 Replies
best response confirmed by Rodney2485 (Copper Contributor)
Solution

# Re: Unique List, excluding certain criteria

=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.

# Re: Unique List, excluding certain criteria

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))``````

1 best response

Accepted Solutions
best response confirmed by Rodney2485 (Copper Contributor)
Solution

# Re: Unique List, excluding certain criteria

=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.