Forum Discussion
Solved
Filter Advanced / Multiple criteria / non contain
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
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...
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).
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...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...
=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.
=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.
