Autofilter with more than two criteria

[w8k17]

Hey everyone,

I have a database, which I want to filter for keywords (using a logical OR) with regards to a certain column. However, I need to be able to specify more than two keywords but the autofilter function does not allow for more than two. So, I found a workaround using Arrays but unfortunately, it neither works for more than two arrays. Here's an example of what I want to do:



If Range("Keyword") <> "" Then
Dim Keywords() As String
Dim Placeholder As String
Dim Count As Integer
Dim Rank As Integer

Keywords = Split(Range("Keyword"), ",")
Rank = UBound(Keywords)

For Count = 0 To Rank
Keywords(Count) = Trim(Keywords(Count))
Placeholder = "=*" & Keywords(Count) & "*"
Keywords(Count) = Placeholder
Next Count

Worksheets("Summary").Range("$A$6:$H$20000").AutoFilter Field:=5, _
Criteria1:=Keywords, Operator:=xlFilterValues
End If


I also attached a spreadsheet so that you can try around with it. What happens is that if you add more than two keywords (separated by a comma each), then it does not use the logical OR for filtering anymore but filters the expressions in the column to be equal to ALL the keywords, which is nonsense.

Can anyone help me out with this? I would really appreciate it!

Thanks in advance and have a nice weekend!

Best,
Alexander

[mperrah]

this uses looping instead of filters.
Not perfect but a start.
It looks specifically for 3 keywords. I am tweaking it to look for random amounts of keywords.
It hides rows with unmatched keywords..
I added an unhide script too.

Sub testFilter()
Dim wsSum As Worksheet
Dim lr, r As Long
Dim myKeywords, firstKey, lastKey, mCnt, k

Set wsSum = Sheets("Summary")

wsSum.Activate

With wsSum
lr = .Cells(Rows.Count, "E").End(xlUp).Row

.Cells.AutoFilter
.Range("A8:A200").EntireRow.Hidden = False ' set to max range

If Range("sWord") <> "" Then

myKeywords = Split(Range("sWord"), ",")
firstKey = LBound(myKeywords)
lastKey = UBound(myKeywords)

For mCnt = 0 To lastKey
myKeywords(mCnt) = Trim(myKeywords(mCnt))
Next mCnt
End If
' this only looks for 3 keyword values and errors if not 3 keywords...
For r = lr To 8 Step -1
If .Cells(r, 5).Value <> myKeywords(0) And _
.Cells(r, 5).Value <> myKeywords(1) And _
.Cells(r, 5).Value <> myKeywords(2) Then
.Cells(r, 5).EntireRow.Hidden = True
End If
Next r
End With

End Sub

Sub unHideRows()

Rows("1:200").Select
Range(Selection, Selection.End(xlDown)).EntireRow.Hidden = False

End Sub

[mperrah]

Ok, this looks for as many keywords you want, but...
it is case sensitive and only finds exact matches
so Welcome. is not welcome or Welcome (no period)

Sub testFilter2()
Dim wsSum As Worksheet
Dim lr, r As Long
Dim myKeywords, firstKey, lastKey, mCnt, k, kCnt

Set wsSum = Sheets("Summary")

wsSum.Activate

With wsSum
lr = .Cells(Rows.Count, "E").End(xlUp).Row

'.Cells.AutoFilter
.Range("A8:A200").EntireRow.Hidden = False ' set to max range

If Range("sWord") <> "" Then

myKeywords = Split(Range("sWord"), ",")
firstKey = LBound(myKeywords)
lastKey = UBound(myKeywords)

For mCnt = 0 To lastKey
myKeywords(mCnt) = Trim(myKeywords(mCnt))
Next mCnt
End If

For r = lr To 8 Step -1
kCnt = 0

For k = firstKey To lastKey
If .Cells(r, 5).Text = myKeywords(k) Then
kCnt = kCnt + 1
End If
Next k

If kCnt = 0 Then
.Cells(r, 5).EntireRow.Hidden = True
End If

Next r
End With

End Sub

[mperrah]

Note, you can type * in the search keyword cell E7 and that will find "Like" results
still case sensitive though

[p45cal]

See attached, uses Advanced Filter, case insensitive, currently up to 18 keywords or phrases (separate any phrases with commas (consequently won't look for commas)) but very easy indeed to increase that limit to more than a million!

[w8k17b]

Hey guys,

thanks to both of you, mperrah and p45cal!

I am currently about to implement p45cal's version and it seems to be working just fine! Awesome. Thanks a million!

Best,
Alexander

[w8k17b]

Hey p45cal,

I have a quick question and I would really appreciate if you could help me out.


The code you provided me with last week is working just fine. Thanks again so much! However, I now want to implement the possibility to search for the keywords using a logical AND instead of a logical OR. To prevent confusion: The relation between the search field "Keyword" and all the others ("Size", "Client", etc.) is a logical AND and always need be. The relation between different keywords, though, is a logical OR at the moment. I want to implement a button such that one can switch between a logical AND and OR when searching for multiple keywords.

Is it possible to slightly adjust the code such that it uses a logical AND instead of a logical OR or would that require rewriting the whole thing?

Thanks in advance!



Best,
Alexander

[p45cal]

I have a quick question and I would really appreciate if you could help me out.The question might be quick but the answer isn't especially so.
See attached.

[w8k17b]

The question might be quick but the answer isn't especially so.
See attached.

Oh my god, you are awesome! Thank you SO much! I deeply appreciate it!

I wish you a nice weekend!

Best,
Alexander

[w8k17b]

Hey p45cal,

just one final question: I implemented your code and it's working perfectly fine. It actually works great. I just don't really understand the difference between the following two lines of code:



If Sheets("Search").ToggleButton1 Then
Sheets("Search").Range("M2").Resize(, UBound(xx) + 1) = xx
Set myCriteriaRng = Sheets("Search").Range("I1:I2").Resize(, UBound(xx) + 5)
myCriteriaRng.Select


Of course, that's the part of the code that is being executed if the button is switched to "AND". It uses rows 1 and 2 of the helper fields as the filter criteria. That makes sense.



Else
Sheets("Search").Range("M2").Resize(UBound(xx) + 1) = Application.Transpose(xx)
Set myCriteriaRng = Sheets("Search").Range("I1:M1").Resize(UBound(xx) + 2)
End If


That part of the code is being executed if the button is switched to "OR". It uses columns I to M as the filter criteria. That also makes sense BUT I don't understand why this is the crucial difference between the logical AND and logical OR search. Can you explain that in a sentence or two to a bloody VBA beginner like me?

Again, thanks a million in advance!

Best,
Alexander

[p45cal]

Sheets("Search").Range("M2").Resize(, UBound(xx) + 1) = xx
Set myCriteriaRng = Sheets("Search").Range("I1:I2").Resize(, UBound(xx) + 5)
myCriteriaRng.Select

Of course, that's the part of the code that is being executed if the button is switched to "AND". It uses rows 1 and 2 of the helper fields as the filter criteria. That makes sense.

That part of the code is being executed if the button is switched to "OR". It uses columns I to M as the filter criteria. That also makes sense BUT I don't understand why this is the crucial difference between the logical AND and logical OR search. Can you explain that in a sentence or two to a bloody VBA beginner like me?


OMG, did I really leave myCriteria.Select in? (I did.) It should be commented out or deleted, but for the moment it's useful to see what the criteria range is if you step through the code with F8 having unhidden those columns.
The AND section uses the headers and only 1 row below to AND the lot. I used multiple headers, one for each criterion; it was easier to adjust the code and the criteria range. I could have a used a single cell with a formula, but I'd have had to construct a formula which resolved to TRUE or FALSE, place that formula in M2 or N2 and be constrained by formula length limits in a cell.
The OR section uses multiple rows in the criteria range but always remains 5 columns wide.

To explain it in a sentence or two - no I can't!
Have a look here: http://www.contextures.com/xladvfilter01.html#CriteriaRange The link should take you to the right spot on the page. There's a lot of good information at that website.

Also see:
http://www.excel-easy.com/examples/advanced-filter.html
https://blog.udemy.com/excel-advanced-filter/


or do a google for
excel advanced filter
You should get loads.

[w8k17b]

Thank you so much, again! You are a life-saver!

Have a pleasant week!

Best,
Alexander