Solved Create a multiple wordsearch and filter function in Excel

March 11, 2016 at 00:56:57
Specs: Windows 7
I would like an option where users can enter various different word into a range (say 15 cells, though they may only use only one or a few of them) These words will then be search for in 2 columns of text. I would also like that the "raw" data would be filtered when the search is completed to only show content that had the searched words.

I have found conditional formatting that allows me to search for one word, and a marco to filter on the searched content. However I can not seem to find a way to search for multiple words/phrases at the same time. Again bearing in mind that 1 user may only have 1 word to search for, another may have 5 and another 10.

I am using Excel 2010

message edited by Elderine


See More: Create a multiple wordsearch and filter function in Excel

Report •


#1
March 11, 2016 at 04:58:14
Could you post the macro or a link to the macro that you say can filter on the searched content? If it already does some of what you need it to do, it may just need some enhancement, such as looping through the list of words in the search range, etc.

It would also help if we had some more details, such as:

- Where is the list of search strings? e.g. Sheet1!A1:A15, etc.
- What columns contain the text to be searched?
- Will the text to be searched contain the only the search string or will "partial searches" be required?

e.g.

Search string = brown dog
Text to be searched = I have a brown dog

- Where do you want the output? e.g. on the same sheet in a different range or on a different sheet, etc.?

Please include any other details that you think are important, keeping mind that we can't see your worksheet from where we are sitting. The more detail you provide, the better.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 11, 2016 at 05:27:12
Thanks DerbyDad03
Please see answers below:

Two Tabs:
1. "Analysis" page using buttons and slicers and where i'd like to add search feature
2. "Raw" - all the raw data is located here

Conditional formating used in Raw Tab:

=OR(ISNUMBER(SEARCH(Searchwords, $D$1&$E$1)))

Search button Macro

Sub Searchfilter()
' Searchfilter Macro

Sheets("Raw").Visible = True 'Makes this page visible
    Sheets("Raw").Select
    ActiveSheet.Range("$A$1:$CK$9999").AutoFilter Field:=12, Criteria1:=RGB(253 _
        , 233, 217), Operator:=xlFilterCellColor
    Sheets("Raw").Select 'Makes this the selected page
End Sub

Where is the list of search strings?
On a tab called Analysis - input range is Q25:Q39
So the user has 15 slots to search for words or phrase. They may not use all 15, so the rest would be blank.

What columns contain the text to be searched?
On a different tab called Raw Column D and E

Will the text to be searched contain the only the search string or will "partial searches" be required?
Any words entered into the range should be searched for. So if "Moon" Night" "time to sleep" were entered into the range, if these terms where located in the text included in Column D and E in the Raw tab, then the raw data should be filtered to show only that information.

Where do you want the output? e.g. on the same sheet in a different range or on a different sheet, etc.?
I didn't know this was possible... but would be excellent if the search data could be outputted to a different tab.

Search Design idea
1. User has 15 cells to enter key words or phrases to search, but may or may not use all 15 cells. (Tab: Analysis)
2. User clicks on search button.
3. Search is carried out on Raw tab columns D and E for the words that have been entered.
4. Filtered Results are displayed along with all the other column data in an output page. Columns go from A to CK


Report •

#3
March 11, 2016 at 07:43:32
✔ Best Answer
Try the following code. You will need a sheet named Output for the well, umm, output. ;-)

I made a few assumptions:

- Your Output sheet has headings in Row 1
- You want the Output sheet cleared (except for the Heading Row) each time the Search button is used.
- The Search strings are "top loaded" in Q25:Q39. (The code stops searching when it finds an empty cell in that range)
- None of the search strings will be found in the Heading Row(s) for Raw!D:E (As written, the code searches the entire columns. That is easily modified.)
- There may be more than one occurrence of a search string in Raw!D:E so the code checks for multiple occurrences and copies the data for each one found.

BTW...I don't know your level of VBA expertise, so may I suggest that you review the Tutorial found here?

http://www.computing.net/howtos/sho...

The debugging techniques discussed can not only help you write and fix VBA code, but they are a great way to reverse-engineer code that you find in forums such as these so that you can modify it for your own use.

Let me know what you think.

Option Explicit
Sub MultiSearch()
Dim cell As Range, c As Range
Dim firstAddress As String
Dim nxtRw As Long
'Clear Output Sheet except for Row 1 Headings
     Sheets("Output").Range("A2:CK" & Rows.Count).ClearContents
'Loop through Analysis!Q25:Q39
    For Each cell In Sheets("Analysis").Range("Q25:Q39")
'Exit if cell is Empty (less than 15 Search Strings)
     If cell = "" Then Exit Sub
'Search Raw!D:E
     With Sheets("Raw").Range("D:E")
      Set c = .Find(cell, lookat:=xlPart)
        If Not c Is Nothing Then
          firstAddress = c.Address
'Determine next empty Row in Output, Copy/Paste data
'Search for multiple occurances of current search string
         Do
           nxtRw = Sheets("Output").Range("A" & Rows.Count).End(xlUp).Row + 1
              Sheets("Raw").Range("A" & c.Row & ":CK" & c.Row).Copy _
                Sheets("Output").Range("A" & nxtRw)
              Set c = .FindNext(c)
         Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
     End With
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 11, 2016 at 08:06:45
Hi,

Thank you, this works great!

Is it possible to search for the exact word/phrase entered?

As an example
If someone searched for alan, and the content contained balance, these results currently are displayed.


Report •

#5
March 11, 2016 at 09:23:08
I guess I need clarification on this question:

Will the text to be searched contain the only the search string or will "partial searches" be required?
Any words entered into the range should be searched for. So if "Moon" Night" "time to sleep" were entered into the range, if these terms where located in the text included in Column D and E in the Raw tab, then the raw data should be filtered to show only that information.

Do the cells in Raw!D:E contains phrases that contain the search strings or are they exact matches. (I assumed you had phrases)

In other words, I assumed you are searching for alan in phrases like these:

I know a guy named Alan.
Alan is a nice guy.
I will invite Alan to my party.
I will balance the number of guys and gals at my party

If so, here is the problem with only finding alan: (We'll ignore Case at this point unless you tell me otherwise)

The .Find function in VBA is the same as the Find feature in Excel. It is either going to look for a match that matches the entire cell: lookat:=xlWhole or it's going to look for the search string anywhere in the cell: lookat:=xlPart (e.g. alan will be found in the middle of balance.)

So, if you want to find alan as part of a phrase, even if it a standalone word, we have to use lookat:=xlPart in the code. Once we use xlPart, alan will be found anywhere in the cell.

OK, so we have some options:

How about we search for " " & alan? (alan with a space in front of it)

That won't find balance, but it also won't find alan at the beginning of the phrase, only in the middle or at the end.

OK, so let's use alan & " ". (alan with a space following it)

That won't find balance, but it also won't find alan at the end of the phrase, only in the middle or at the beginning.

So how about we search once for " " & alan and once for alan & " ". That will find all standalone alan's but it will also find the ones in the middle twice since those alan's have a space before and after it. :-(

So how do we find a standalone alan when doing a Partial Search? I think it can be done by using some redundancy checking, but before I spend (waste) any time working on that, I need to know if that is actually a problem.

If the Raw data only contains an exact match for the Search strings, then just change xlPart to xlWhole and you should be good to go. If not (i.e. if it looks my examples above) then we have some work to do.

Perhaps some examples of your actual data and search strings might help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
March 14, 2016 at 03:55:08
Hey thanks for your response. After thinking about it a bit more I think that your original solution works just fine.

Thank you so much for your help.

Alan


Report •

Ask Question