Wildcard for Dynamic Filter

[Chewyhairball]

Hi

I have set up a dynamic table filter from the following and it works really well;

Dynamic Excel Filter Search Box (Extract data as you Type)

Learn how to create a Dynamic Excel Filter Search Box. You can type the data in the search box and it will instantly filter the Excel Data.

trumpexcel.com

If I start to type a word my list of data filters and shows anything with the word I have used

I then found out if I put an "*"between the search words it would find both and display as below

This is great but what I would really like but cannot figure it out is how to search for multiple terms as above but allow the words to be in any order.
So far all I have managed is for it to work if the words are in order. I would like to be able to for example type "excavation" and then "wastes" and for it to show me the same data.

Not sure if it is useful but the search formula is
IF(ISNUMBER(SEARCH($H$24,B25)),E25,"")
where H24 is the search cell (filled from the combobox)

I also tried a variation of the above formula using wildcard characters but it didn't work as I wanted
IF(ISNUMBER(SEARCH("*"&$H$24&"*",B25)),E25,"")

thanks

 

[GlennUK]

My pleasure. I'll keep that in my toolbox of useful thingies.

 

[Chewyhairball]

Hi, The above works great. I have discovered another sheet where a similar thing would be handy albiet with a slight alteration.

So the above lets me type a word and as i type it filters the list...i then type another word and if that word and the first word both appear in the text then it filters them...etc...

Is it possible to have it so it filters all instances of all search words.
i.e I type 'Water' for example and it filters all rows with water in the text. I then type 'Oil' and it then also shows all rows with oil in the text.
I dont want it to only filter rows with both words ( as above) but all instances of each word.

I have tried modifying what i have but cannot figure it out.

Thanks

Rory

 

[GlennUK]

Change the =$Q$2 to be >0 I think.

 

[Chewyhairball]

hmmm! i cant get that to work.

 

[Chewyhairball]

I got it. I changed $Q2$ to >0 as you stated above but i also had to change the
Q2 code

LEN('Waste Codes'!C3)-LEN(SUBSTITUTE('Waste Codes'!C3,"/",""))+1

to

 IF(COUNTA(Q3:Q6)-COUNTBLANK(Q3:Q6)=0,1,(COUNTA(Q3:Q6)-COUNTBLANK(Q3:Q6)))

It counts the number of words used and if no words are used then it puts 1 in the cell.

This seems to get me exactly what i need but don't ask me to try and explain it

thanks again Glenn

 

[GlennUK]

That's great!