Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Filter Advanced / Multiple criteria / non contain

Copper Contributor

Hi All

 

I need to filter out from the column A (a list of emails) all the non business emails. 

In the column D I have the criteria (private emails to be excluded).

In the real case both column A and D are made of thousands items. (I have found in internet a list with  thousands of private email providers domains).

If I use the Advanced filter as below, I can not get the desired effect.. probably because I can not add all the items of the criteria in the same column?

 

thx

 

 

 

filter.jpg

9 Replies

@MarcoTorinoi 

=IF(SUM(N(ISNUMBER(SEARCH($D$2:$D$4,A2)))),"",A2)

 

A simple off-the-cuff alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

filter multiple criteria.png

 

If you work with a recent version of Excel you can apply the FILTER function. 

@MarcoTorinoi When using multiple "does not" criteria with Advanced Filter, you need to use "And" logic by arranging them horizontally on a single row with the column header ("email") repeated for each one:

 

Advanced Filter Does Not Contain ListAdvanced Filter Does Not Contain List

 

With the criteria listed vertically in a single column you're using "Or" logic, which returns ALL records because <>"*@gmail.*" returns TRUE for all "yahoo" addresses and <>"*@yahoo.*" returns TRUE for all "gmail" addresses (they cancel each other out).

@OliverScheurich 

thanks..but unfortunatelly the formula does not work. you see any error?

Capture.PNG

Thanks.. the issue is that I have tried to transpose from vertical to horizontal the list of the private emails (https://gist.github.com/ammarshah/f5c2624d767f91a7cbdc4e54db8dd0bf ) and gives an error.
It is probably because Excel can not transpose a list of 6000 items...

best response confirmed by MarcoTorinoi (Copper Contributor)
Solution

@MarcoTorinoi Please see the attached workbook with the list of email provider domains arranged horizontally. With such a huge criteria list, performance may be an issue when applying it to a large dataset. You'll just have to test it to see how well it performs...

@djclements 

 

I can't copy it :(

Capture.PNG

@MarcoTorinoi 

=IF(SUM(N(ISNUMBER(SEARCH($D$2:$D$3,A2)))),"",A2)

 

There are 2 criteria in your example in range D2:D3 therefore the formula has to reference D$2:$D$3.

 

Additionally since you don't work with Office 365 or Excel for the web or Excel 2021 you have to enter the formula as an arrayformula with ctrl+shift+enter. This will put the curly brackets {  } around the formula. You can see the brackets highlighted in yellow in the screenshot from my Excel 2013 worksheet.

filter.png

@MarcoTorinoi I'm not exactly sure where you went wrong with the copy/paste procedure. This is what I did:

  • import the list into column A (range A1:A6104)
  • add the "does not contain" syntax using a formula in cell B1 (="<>*"&A1&"*") and copy it down (range B1:B6104)
  • select the entire list in range B1:B6104 and press Ctrl+C to copy
  • select cell C1 and press Ctrl+Alt+V to open the Paste Special dialog box
  • select "Values" and "Transpose" and click "OK"

Paste Special Values > TransposePaste Special Values > Transpose

 

Thanks.. I did the same. but I succeed to transpose a maximum of 250 items. Not sure why,
1 best response

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

@MarcoTorinoi Please see the attached workbook with the list of email provider domains arranged horizontally. With such a huge criteria list, performance may be an issue when applying it to a large dataset. You'll just have to test it to see how well it performs...

View solution in original post