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/xladvfilt...#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