Finding/flagging words using a keyword list??

Question

Hi, how can I find/tag/copy words from a list using a keyword search? My keywords are in one sheet and I want to flag all the rows in which one of the columns contain a word that's in my list of keywords. I'm able to do this with exact matches, but how can I do this so I'll find also partial matches? Example: in my keyword list is "zzz" how can I get the word "xxzzzyy" to be flagged with the keyword "zzz"?

I'm using a macro for this search..

Answer 1

Looking at your macro, it occured to me that you have Keywords to search for in column A and not in column G as I thought.

I have swithched the references now so it should be working now!

Your macro is absolutely not as efficient as mine, but it can be speeded up a bit if we turn off screenupdating and set calculation to manual temporary. (I have used the same approach in the macro below):

Sub testIt()

Dim FullList As Range

Dim SearchList As Range

Dim FilterRng As Range

Dim TargetRng As Range

Dim cell As Range

With Application

    .ScreenUpdating = False

    .Calculation = xlCalculationManual

End With

Set FilterRng = Range(Range("G1"), Range("G1").End(xlDown))

Set SearchList = Range(Range("A2"), Range("A2").End(xlDown)) 'Header in A1

Set TargetRng = Range(Range("G2"), Range("G2").End(xlDown))

Cells.Interior.Pattern = xlNone

For Each cell In SearchList

    FilterRng.AutoFilter Field:=1, Criteria1:="=*" & cell.Value & "*"

      If FilterRng.SpecialCells(xlCellTypeVisible).Count > 1 Then

        TargetRng.SpecialCells(xlCellTypeVisible).EntireRow.Interior.Color = 65535 'Yellow

    End If

Next

FilterRng.AutoFilter

With Application

    .ScreenUpdating = True

    .Calculation = xlCalculationAutomatic

End With

End Sub

Answer 2

Then I think the best solution is to use an AutoFilter:

Sub test()

Dim FullList As Range

Dim FilterRng As Range

Dim TargetRng As Range

Dim cell As Range

Set FilterRng = Range(Range("A1"), Range("A1").End(xlDown))

Set SearchList = Range(Range("G2"), Range("G2").End(xlDown)) 'Include header

Set TargetRng = Range(Range("A2"), Range("A2").End(xlDown))

TargetRng.EntireRow.Interior.Pattern = xlNone

For Each cell In SearchList

    FilterRng.AutoFilter Field:=1, Criteria1:="=*" & cell.Value & "*"

    TargetRng.EntireRow.Interior.Color = 65535 'Yellow

Next

FilterRng.AutoFilter

End Sub

Answer 3

Sorry guys for being so confusing. I was just trying to explain that the method on how I find the keywords from the data is not the important thing here. If the information is copied else where etc.it's ok, but I'd prefer to just change the color of the row in which the keyword is found. Here's the code I've been working on:

Sub test()

Dim fulllist As Range, searchlist As Range, c As Range, cfind As Range

Range(Range("H2"), Range("H2").End(xlDown)).Cells.Clear

Set fulllist = Range(Range("A1"), Range("A1").End(xlDown))

Set searchlist = Range(Range("G2"), Range("G2").End(xlDown))

For Each c In searchlist

Set cfind = fulllist.Find(what:=c.Value, lookat:=xlWhole)

If Not cfind Is Nothing Then

c.Offset(0, 1) = "!!!!!"

Else

c.Offset(0, 1) = "ok"

End If

Next c

End Sub

This basically puts an "ok" to each data row checked and "!!!!!" if there's a keyword found. My problem with this is that I'll only find exact matches and this is not a feasible solution in my case. I need the macro to be able to also find partial matches. Example. my list of keywords contains "zzz" I want my macro to be able to find this if my data contains "xxxzzzyyy".

If I use xlPart instead of xlWhole it will find all the letters that the keyword/keywordlist contains so in my case it's the whole alphabet.

The placement of the list of words is not a big problem I can include the placement in the code easily.

I hope this clarifies my problem better. Thanks for all the help in advance!

Answer 4

Hi, how can I find/tag/copy words...

Tag? Copy? Exactly what are you trying to do?

 My keywords are in one sheet...

Which sheet? Where on the sheet?

...and I want to flag all the rows in which one of the columns contain a word that's in my list of keywords

Initially you said tag/copy, now you are saying flag... again, exactly what are you trying to do?

I'm able to do this with exact matches, but how can I do this so I'll find also partial matches?

Can you show us your code for this part?

Answer 5

You can use the InString function. See the example below:

a = "zzz"

b = "cccserzzz"

If InStr(1, b, a) > 0 Then

    msg = MsgBox("Partial Match")

End If

You should as always post your code for comments.