Wildcard for Dynamic Filter

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
202
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

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

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


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


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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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

Board Regular
Joined
Nov 30, 2017
Messages
202
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Have a look at these for ideas:
 

Attachments

  • CompboFilt1.JPG
    CompboFilt1.JPG
    84.6 KB · Views: 10
  • CompboFilt2.JPG
    CompboFilt2.JPG
    35.4 KB · Views: 10

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
202
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Take a look at tweak:
 

Attachments

  • CompboFilt3.JPG
    CompboFilt3.JPG
    44 KB · Views: 14
Solution

Forum statistics

Threads
1,147,732
Messages
5,742,854
Members
423,759
Latest member
meb229

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top