Filtering all data by one column that CONTAINS a defined set of words

[Dougmeister]

I have a list of eleven (11) terms by which that I need to filter the data in column "C". If any row contains any of these terms, I want to show only those rows.

I can keep it on the same tab.

I need to repeat this every month with new data.

What is the easiest way to do this?

 

[Marcelo Branco]

Maybe using Advanced Filter but we need more information to help.
Try to show us a small data sample, including the list of terms, along with expected results.

M.

 

[shg]

I'd add a column with a formula and filter on that:

	A​	B​	C​
1​		sheep
2​		dog
3​		cat
4​
5​	Data​	Check​	​
6​	A wolf in sheep's clothiong	1​	B6: =SUMPRODUCT(COUNTIF(A6, "*" & $B$1:$B$3 & "*"))
7​	How now brown cow?	0​
8​	Who let the dogs out?	1​
9​	The cow jumped over the moon	0​
10​	He's a cool cat	1​

 

[Dougmeister]

How is this? Filter Screenshot #1 ("Books") byScreenshot #2 ("Abbreviations") to show only the books inScreenshot #3 ("Books (filtered)").

(I didn't know how to create a table like shg did, so here are screenshots...)

Screenshot #1

Screenshot #2

Screenshot #3

This is greatly simplified. The actual data to use as the filter will contain at least 100 phrases/abbreviations. As the list will change occasionally, I think that performing this filtering via VBA would be best.

 

[Marcelo Branco]

Maybe something like this

1. First of all:
Change the value of A5 in sheet Abbreviations from JS to (JS)

2. Go to Sheet Books
put in C1
Check

Formula in C2
=--(SUMPRODUCT(--ISNUMBER(FIND(" "&Abbreviations!$A$2:$A$7&" "," "&B2&" ")))>0)

3. Go to sheet Books (Filtered) and follow these steps

Data > Advanced Filter and set the parameters as
List Range:Books!$A$1:$B$11
Criteria Range: Books!$C$1:$C$2
pick Copy to another location and
Copy to: 'Books (Filtered)'!$A$1
Ok

(you can select the proper ranges to set the parameters)

Hope this helps

M.

 

[Dougmeister]

Thanks!

Is there a way to recalculate the output without going into the Excel menu options ("Data > Advanced Filter", etc.)? By putting some code in a VBA module? Keyboard shortcut to recalculate, etc.?

 

[Marcelo Branco]

Turn-on the macro recorder;
Do all the process manually (insert header in C1, formula in C2. etc etc);
Turn-off the macro recorder
You've gotten the code!

M.

 

[Marcelo Branco]

With some adjustments in the recorded code, i've gotten this code. It worked for me.

Sub AdvFilter()
    Dim lastRow As Long
    
    With Sheets("Books")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("C1") = "Check"
        .Range("C2").Formula = _
        "=--(SUMPRODUCT(--ISNUMBER(FIND("" ""&Abbreviations!A$2:A$7&"" "","" ""&B2&"" "")))>0)"
    End With
    
    Sheets("Books (Filtered)").Select
    Sheets("Books").Range("A1:B" & lastRow).AdvancedFilter _
        Action:=xlFilterCopy, CriteriaRange:=Sheets("Books").Range("C1:C2"), _
        CopyToRange:=Range("A1"), Unique:=False
End Sub

M.

 

[Dougmeister]

Is there a limit to the number of rows this method can handle? It works fine for my sample, but when I use my actual data, there are over 8,000 rows and it ends up returning rows that do not match the search terms on the "Abbreviations" tab.

I neglected to mention (and I apologize) that some of the "abbreviations" have spaces in the middle (i.e., can be multiple "words"). Does that change anything?

To get more results, I also had to change this line:

=--(SUMPRODUCT(--ISNUMBER(FIND(" "&Abbreviations!$A$2:$A$31&" "," "&B2&" ")))>0)

to:

=--(SUMPRODUCT(--ISNUMBER(FIND(Abbreviations!$A$2:$A$31,B2)))>0)

 

[Marcelo Branco]

The macro was created considering the sample data you showed us. If your actual case is different, it will not work. But I think you may have understood the logic and that you can adapt it.
Always try to show a sample that really matches your data.

M.