Autofilter with multiple conditions (VBA)

[David2]

I'd like to sort my data (A1:D10 with headers) by values in column B.


I want to only show entries that do not contain 123 or ABC or 456 or DEF in column B (entries can be at the start, middle or end therefore I'm guessing I'd need to use * wildcards).


Autofilter seems to limited to two conditions so I'm wonder how do I fit four OR conditions here? Array perhaps?

Thank you.

 

[jmacleary]

Hi there.

If you can add an additional column (E?), put this formula in it =SUMPRODUCT(COUNTIF(B2,{"*123*";"*ABC*";"*456*";"*DEF*"}))>0 then drag down and filter that column for FALSE. What id does is count how many matches occur in B2 for any of the searches. If any are found the count is greater than 0 and evaluates to TRUE. If you cant add an additional column, it is still possible to achieve, but you will need to use VBA (I can help if thats the case).
John

 

[Fluff]

You can't use four conditions, as you said it's limited to two.
Have a look at advanced filters https://www.contextures.com/xladvfilter01.html

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...filter-macro-with-multiple-or-conditions.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[David2]

I apologize Fluff.

Hi there.

If you can add an additional column (E?), put this formula in it =SUMPRODUCT(COUNTIF(B2,{"*123*";"*ABC*";"*456*";"*DEF*"}))>0 then drag down and filter that column for FALSE. What id does is count how many matches occur in B2 for any of the searches. If any are found the count is greater than 0 and evaluates to TRUE. If you cant add an additional column, it is still possible to achieve, but you will need to use VBA (I can help if thats the case).
John

jmacleary , that's a very elegant solution! Can I use it to filter A1:B2 entries on Sheet1, while having the True/False helper column positioned on Sheet2?

I've modified the formula on Sheet2 to check Sheet1 data and assign T/F values =SUMPRODUCT(COUNTIF(Sheet1!B2,{"*123*";"*ABC*";"*456*";"*DEF*"}))>0

However I don't know how to use that to create AutoFilter for Sheet1.

 

[David2]

Also, what how I achieve this filtering by using VBA?

Thanks!

 

[jmacleary]

Also, what how I achieve this filtering by using VBA?

Thanks!

Hi David. I suggest you look at advanced filters first, as Fluff suggests above.

 

[David2]

Hi David. I suggest you look at advanced filters first, as Fluff suggests above.

I'll have a look at advance filters. I'm guessing I can't have the helper column on another sheet and use a regular filter?

 

[jmacleary]

Hello again. No the filter would have to be on a column in the filtered data, i.e. on sheet1.