Filter with more than 2 criteria

[jonascruz]

Hello . When I filter the column with only two criteria , it works well . But when I add 1 more criteria , it does not display the desired result. Does anyone know how I can filter 3 text criteria in this column ?



Materials (Table)
Office chair
Blue Computer Chair
Red Computer Chair
Pink Computer Kids Chair
Set of pens
Post it
Case
Notebook with logo
Black pencil 2
Black pencil 5
Transparent ruler
Opaque ruler

Sub filterTextNcriteria()
'
' filterTextNcriteria Macro
'
ActiveSheet.Range("A2:A13").AutoFilter Field:=1, Criteria1:=Array("*chair*", "black*", "*ruler*"), Operator:=xlFilterValues
'ActiveSheet.Range("A2:A13").AutoFilter Field:=1, Criteria1:=Array("*chair*", "black*"), Operator:=xlFilterValues
End Sub

 

[Jonmo1]

Autofilter can only do 2 'Or' criteria when using the 'contains' options.
I mean when you use wildcards on your criteria "*chair*", that is making the autofilter use the custom text filter option 'Contains'
It can only do 2 of those.

You'll probably need to use a helper column with a formula to determine if the cell contains any of the 3 words..

Say in B2 and filled down
=SUM(COUNTIF(A2,{"*chair*", "black*", "*ruler*"}))>0

Then filter on column B for TRUE.

 

[jonascruz]

What did you mean with this?

[COLOR=#333333]{"*chair*", "black*", "*ruler*"}[/COLOR]

 

[Jonmo1]

It's an array of 3 criteria strings for countif

 

[jonascruz]

I could adapt this array directly in the column where I would like to show the information. I just had trouble changing the formula for Excel Brazil because it is a little different, but it all worked out .Thanks a lot for the help.
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">I could adapt this array directly in the column where I would like to show the information. I just had trouble changing the formula for Excel Brazil because it is a little different, but it all worked out .Thanks a lot for the help.</body>

 

[Jonmo1]

You're welcome.