Solved

Using VBA to filter Excel column based on multiple keywords

Posted on 2011-09-18
5
517 Views
Last Modified: 2012-05-12
Using VBA I need a function to filter an Excel spreadsheet based on a string of keywords. The rows need to contain (all) the keywords, but not necessarily in the same order as listed.

There is a previous question/answer that partially addresses this, but it returns the rows that contain (any) of the keywords, not all of them.

I'm searching only column 'B'

Below is the reference to the previous entry for reference.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_21740621.html?sfQueryTermInfo=1+10+30+filter+keyword+multipl 
'Credit Webtubbs at above solution

Sub ShowMatches()
     Dim r As Long
     Dim LastRow As Long
     Dim SearchCriteria As String
     Dim arr() As String
     Dim i As Long
     Dim HideRow As Boolean

     SearchCriteria = InputBox("Enter your required Search Criteria" & vbCrLf & _
                               vbCrLf & "[Seperate Search items with a semi-colon ( ; )]")
     If SearchCriteria = "" Then Exit Sub
     Application.ScreenUpdating = False
     arr = Split(SearchCriteria, ";")
     LastRow = [D65536].End(xlUp).Row + 1
     For r = LastRow To 2 Step -1
          For i = LBound(arr) To UBound(arr)
               If InStr(1, Cells(r, "D"), arr(i)) > 0 Then
                    HideRow = False
               End If
          Next
          Rows(r).Hidden = HideRow
          HideRow = True
     Next
     Application.ScreenUpdating = True
End Sub

Open in new window

0
Comment
Question by:gedwardnelson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 36557862
I made the requested changes to your code but without any data I have not tested it.

I noted that the code you attached was searching column d not B so I have changed that as well.

If the code below does not work could you please provide some sample data to test against

Michael
Sub ShowMatches()
     Dim r As Long, LastRow As Long, i as Long, count as long
     Dim SearchCriteria As String
     Dim arr() As String
     Dim HideRow As Boolean

     SearchCriteria = InputBox("Enter your required Search Criteria" & vbCrLf & _
                               vbCrLf & "[Seperate Search items with a semi-colon ( ; )]")
     If SearchCriteria = "" Then Exit Sub
     Application.ScreenUpdating = False
     arr = Split(SearchCriteria, ";")
     LastRow = Range("B" & rows.count).End(xlUp).row + 1
     For r = LastRow To 2 Step -1
          count = 0
          For i = LBound(arr) To UBound(arr)
               If InStr(1, Cells(r, "B"), arr(i)) > 0 Then count = count + 1
          Next
          if count = UBound(arr) then HideRow = False
          Rows(r).Hidden = HideRow
          HideRow = True
     Next
     Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:gedwardnelson
ID: 36557953
Michael,

Thanks for the quick response. It doesn't appear to search for the second keyword. I have attached a small example of the file.

A couple of examples of what I might be searching for is
2'' X 1'' PVDF REDUCER
or
2'' 316L SS O2 CLEAN TUBING

I might enter something like 2;PVDF and this would list all rows with a "2" AND "PVDF." Or I might enter PVDF;Reducer and get all rows with "PVDF" and "REDUCER", or I might enter 2;SS;Tubing. Or I might just enter a single keyword of "PVDF." Each of these entries might result in more than each of the items listed above but it would narrow down the list significantly, or could actually result in the exact item.

I forgot to mention that this should not be case-sensitive because there hasn't been any consistency in how items were entered.

George
FilterTest.xls
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 400 total points
ID: 36559164
Small reversal of the original logic:
Sub ShowMatchesOrig()
   Dim r                 As Long
   Dim LastRow           As Long
   Dim SearchCriteria    As String
   Dim arr()             As String
   Dim i                 As Long
   Dim HideRow           As Boolean

   SearchCriteria = InputBox("Enter your required Search Criteria" & vbCrLf & _
                             vbCrLf & "[Seperate Search items with a semi-colon ( ; )]")
   If SearchCriteria = "" Then Exit Sub
   Application.ScreenUpdating = False
   arr = Split(SearchCriteria, ";")
   LastRow = Cells(Rows.count, "B").End(xlUp).Row
   For r = LastRow To 2 Step -1
      HideRow = False
      For i = LBound(arr) To UBound(arr)
'         If InStr(1, Cells(r, "B"), arr(i), vbTextCompare) = 0 Then
         If InStr(1, Cells(r, "B"), arr(i)) = 0 Then
            HideRow = True
            Exit For
         End If
      Next i
      Rows(r).Hidden = HideRow
      HideRow = True
   Next
   Application.ScreenUpdating = True
End Sub

Open in new window


Note this is case sensitive. If you don't want that, uncomment the commented line and comment the one below it.
0
 
LVL 23

Assisted Solution

by:Michael Fowler
Michael Fowler earned 100 total points
ID: 36559413
George

Rorya has nailed it and deserves the points, I just want to see where I went wrong and thought I would post the corrected code

Michael
Sub ShowMatches()
     Dim r As Long, LastRow As Long, i As Long, count As Long
     Dim SearchCriteria As String
     Dim arr() As String
     Dim HideRow As Boolean

     SearchCriteria = InputBox("Enter your required Search Criteria" & vbCrLf & _
                               vbCrLf & "[Seperate Search items with a semi-colon ( ; )]")
     If SearchCriteria = "" Then Exit Sub
     Application.ScreenUpdating = False
     arr = Split(SearchCriteria, ";")
     LastRow = Range("B" & Rows.count).End(xlUp).Row
     For r = LastRow To 2 Step -1
          count = 0
          HideRow = True
          For i = LBound(arr) To UBound(arr)
               If InStr(1, Cells(r, "B").Value, arr(i), vbTextCompare) > 0 Then count = count + 1
          Next
          If count = UBound(arr) + 1 Then HideRow = False
          Rows(r).Hidden = HideRow
     Next
     Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Closing Comment

by:gedwardnelson
ID: 36561249
Thanks Rorya. That worked exactly as I needed. I made the change you suggested to make it non-case-sensitive.

Michael thanks for your help too. I awarded points to Rorya as you suggested, but I wanted you and anyone else that may be looking at this that your final solution worked too.

George
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question