Filter for multiple text words

Frankinnj

New Member
Joined
Oct 22, 2010
Messages
2
I have a long list of item descriptions that I need to filter down to descriptions containing words like "valve" or "ring" or "root" or "epic" (17 key words in all). The basic custom filter looks like it only lets me filter for 2 words.

I have some experience working with advanced filter for things like >100 but that's about it. Any help with this would be so appreciated.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
YOu can add another column to your data set that compares the key cell in each row with your list of 17 words stored elsewhere, perhaps another sheet.

The formula demonstrated here is an array formula, you must use CTRL-SHIFT-ENTER to confirm the formula and activate the array.

Once this column is active, you can do an AutoFilter by TRUE or FALSE.

Excel Workbook
ABCDEFGH
1A valve is hereTRUEValve
2Nothing hereFALSEPipe
3Pipe is in hereTRUERing
4Nothing hereFALSERoot
5There is a ring hereTRUEEpic
Sheet1
 
Upvote 0
I have been facing the same issue. But the above formula is not working in my case.

I have a list of some workgroups and so I'm trying to filter the records which matches with the given criteria.

Below is the sample list for your help. Any help would be highly appreciated.

Criteria
Matches pattern
t3
qwe
sr


List
itam.qwe
vc.t3
app.qwe
fs.t3
sd.sr
ps.qwe
app.busy
sd.qwe
vc.t3
app.qwe
fs.t3
sd.sr
itam.sr
app.busy
itam.qwe
vc.t3
app.qwe
i.t3
sd.sr
am.qwe
z.sr
e.sr
t1.sr
 
Last edited:
Upvote 0
The answer is the same as in post #2. Put your short list in column H, your long list in column A. Array formula given still goes in column B, filter for TRUE.
 
Upvote 0
Expanding upon post #2, here is a formula to search for key words. Hope this helps.
Mike Szczesny


Excel 2012
ABCD
1The ring is goneringvalve
2That is the boxboxring
3What are some wordswordskite
4No moremoreepic
5The valve is gonevalvesome
6Square root#N/Amore
7I have no wordswordsbox
8That is epicepicwords
Sheet1
Cell Formulas
RangeFormula
B1=LOOKUP(99^99,SEARCH($D$1:$D$8,A1),$D$1:$D$8)
 
Upvote 0
No response yet! :(

If you are going to use the criteria in autofilter...

Row\Col
A​
B​
1​
CRITERIA
2​
t3
3​
qwe
4​
sr
5​
ITEMFILTER
6​
itam.qwe
TRUE​
7​
vc.t3
TRUE​
8​
app.qwe
TRUE​
9​
fs.t3
TRUE​
10​
sd.sr
TRUE​
11​
ps.qwe
TRUE​
12​
app.busy
FALSE​
13​
sd.qwe
TRUE​
14​
vc.t3
TRUE​
15​
app.qwe
TRUE​
16​
fs.t3
TRUE​
17​
sd.sr
TRUE​
18​
itam.sr
TRUE​
19​
app.busy
FALSE​
20​
itam.qwe
TRUE​
21​
vc.t3
TRUE​
22​
app.qwe
TRUE​
23​
i.t3
TRUE​
24​
sd.sr
TRUE​
25​
am.qwe
TRUE​

In B6 enter and copy down:

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH("."&$A$2:$A$4&".","."&$A6&".")))

If A2:A4 must serve as an exclude list, change the formula to:

=ISNA(LOOKUP(9.99999999999999E+307,SEARCH("."&$A$2:$A$4&".","."&$A6&".")))

The criteria list can also be used in advanced filter or in creating a sublist of results by means of a formula which refers to it.

Note. 9.99999999999999E+307 is not an arbitrary big number (http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html).
 
Upvote 0
Thanks A lot Excel Geniuses !!

Both the solutions are working perfectly provided by Mike and Aladin.

Once question here why do we used arbitrary number here.



If you are going to use the criteria in autofilter...

Row\Col
A​
B​
1​
CRITERIA
2​
t3
3​
qwe
4​
sr
5​
ITEMFILTER
6​
itam.qwe
TRUE​
7​
vc.t3
TRUE​
8​
app.qwe
TRUE​
9​
fs.t3
TRUE​
10​
sd.sr
TRUE​
11​
ps.qwe
TRUE​
12​
app.busy
FALSE​
13​
sd.qwe
TRUE​
14​
vc.t3
TRUE​
15​
app.qwe
TRUE​
16​
fs.t3
TRUE​
17​
sd.sr
TRUE​
18​
itam.sr
TRUE​
19​
app.busy
FALSE​
20​
itam.qwe
TRUE​
21​
vc.t3
TRUE​
22​
app.qwe
TRUE​
23​
i.t3
TRUE​
24​
sd.sr
TRUE​
25​
am.qwe
TRUE​

In B6 enter and copy down:

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH("."&$A$2:$A$4&".","."&$A6&".")))

If A2:A4 must serve as an exclude list, change the formula to:

=ISNA(LOOKUP(9.99999999999999E+307,SEARCH("."&$A$2:$A$4&".","."&$A6&".")))

The criteria list can also be used in advanced filter or in creating a sublist of results by means of a formula which refers to it.

Note. 9.99999999999999E+307 is not an arbitrary big number (http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html).
 
Upvote 0
I tried the below solution which seems to work fine.

However, I’m struggling with 2 things

  1. I have that set for 1 cell.. but don’t know how to expand it to broad number of cells (4,500+). When I copy and paste it, I have to change values in each cell on it’s own which is impossible, and when I drag down the formula from the corner of the cell, it shifts down the (keywords) cells in the formula by one.. and corrupts the outcome.
  2. For the array.. I have to do it for each cell separately one at a time as well, and when I select the whole column, I get all values as either True or False, so it doesn’t work.. any ideas how I can work this out?
Thanks,
Hesham



YOu can add another column to your data set that compares the key cell in each row with your list of 17 words stored elsewhere, perhaps another sheet.

The formula demonstrated here is an array formula, you must use CTRL-SHIFT-ENTER to confirm the formula and activate the array.

Once this column is active, you can do an AutoFilter by TRUE or FALSE.

Sheet1

ABCDEFGH
1A valve is hereTRUE Valve
2Nothing hereFALSE Pipe
3Pipe is in hereTRUE Ring
4Nothing hereFALSE Root
5There is a ring hereTRUE Epic

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 159px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B1{=OR(ISNUMBER(SEARCH($H$1:$H$5, A1)))}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top