Wildcard for Dynamic Filter

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
199
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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Chewyhairball

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

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
199
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I got it. I changed $Q2$ to >0 as you stated above but i also had to change the
Q2 code
Excel Formula:
LEN('Waste Codes'!C3)-LEN(SUBSTITUTE('Waste Codes'!C3,"/",""))+1

to
Excel Formula:
 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 o_O

thanks again Glenn
 

Forum statistics

Threads
1,144,241
Messages
5,723,198
Members
422,482
Latest member
MacSapper

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