Filter Advanced / Multiple criteria / non contain

[MarcoTorinoi]

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

[#2]

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

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

[#3]

@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 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).

[#4]

@OliverScheurich 

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

[#5]

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

[#6 · Accepted 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...

[#7]

@djclements 

I can't copy it :(

[#8]

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

[#9]

@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 > Transpose

[#10]

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