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]

Would you want an entry to appear as per your list, if only one of the words matches ... i.e. either waste or exca ... or is that both should match?

 

[Chewyhairball]

Would you want an entry to appear as per your list, if only one of the words matches ... i.e. either waste or exca ... or is that both should match?

Hi

I would like it to filter for the first word. I would then type the second word and if that is in the text filter it a second time with both words.

That's what it does at the moment but it only filters the second word if it appears in the text after the first.

If i type waste then all rows with the word waste filter and then if i type excavation this filters further so i have all rows with the words 'waste' and 'excavation'
but only if the word'waste' is first in the sentence.

If the word'excavation' appears before the word'waste' then it only filters the word waste.

thanks

Rory

 

[GlennUK]

Have a look at these for ideas:

 

[Chewyhairball]

Have a look at these for ideas:

Hi Glenn

What is the Q referencing in your formula?

thanks

 

[GlennUK]

Hi Glenn

What is the Q referencing in your formula?

thanks

Column Q is the word list ... as per picture 2. Offset starts at the first word.

The number in Q2 is the number of words.

 

[Chewyhairball]

Column Q is the word list ... as per picture 2. Offset starts at the first word.

The number in Q2 is the number of words.

OK. I managed to get this working and it solves my word order issue. I would however only like it to show the data with just these words.

When the first word it typed it shows all data with that word somewhere in the sentence.
When the second word is typed it shows all the data with 'both' of the words in the same sentence.
etc

thanks

Rory

 

[GlennUK]

Yeah, I just realised I hadn't done that part ... honestly, I had a dream last night that I'd completed it ... must remember to check things. I'll get right on it.

 

[GlennUK]

Take a look at tweak:

 

[Chewyhairball]

Take a look at tweak:

Yeah, I just realised I hadn't done that part ... honestly, I had a dream last night that I'd completed it ... must remember to check things. I'll get right on it.

Awsome! Exactly what i need. Thank you so much Glenn.

Rory