Matches using a list of keywords

[Closed]
Report
-
 Matt -
Hello,
Can you guys help me with this one?

I have a list of keywords and I want to find all the rows that contains any words from my list. Example:

Peter's groceries <--These should be highlighted or copied to another sheet
Simon's pet store <--These should be highlighted or copied to another sheet
Jimm's PC store <--These should be highlighted or copied to another sheet
K-Mart
7-Eleven

In my keyword list I have words: store and groceries (I have 100+ words on my list). And I want to either highlight the first 3 rows or copy the information the hole row consist to another sheet.

My biggest problem is that I should be able to find rows that contain the keyword in it. Example: yyystorexxx

Thanks a lot for any help!

Matt




4 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
not clear. let me rephrase

you have got a FULLLIST in column A

you want to know whether this FULLLIST contains any of the items in another lists called "searchlist" for e.g. in column G

download this file from this webpage(this is after running the macro test given below)
for retest you can again run the macro

http://speedy.sh/ySfWz/matt.xls

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) = "yes"
Else
c.Offset(0, 1) = "no"
End If
Next c
End Sub
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Thanks a lot Venkat1926! This was great, but I still have a problem with it, I should be able to find partial matches, this only finds me exact matches.

I mean if my keywordlist contains mr it should be able to detect Mr.Penn from the list. Is this possible?
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
make this small change and see

there is a statnent

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

change that xlwhole to xlpart
and save the file
DO THIS IN A DUPLICATE VERSION OF THE FILE
now run the macro and check
I think I figured it out.. Can you find space for improvements in this? I think this might be a bit heavy when the data is really large:

Sub test2()
Dim fulllist, searchlist, c, cfind, d 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 fulllist
For Each d In searchlist
Set cfind = d.Find(what:=c.Value, lookat:=xlPart)
If Not cfind Is Nothing Then
cfind.Offset(0, 1) = "yes"
Else
If d.Offset(0, 1).Value = "" Then
d.Offset(0, 1) = "no"
End If
End If
Next d
Next c

End Sub
Hi, I already tried this and the problem is that when I change xlWhole to xlPart it finds everything that contain any letter from the keyword list.

My keyword list contains so many phrases that it also contains the whole alphabet so it finds a match on everything.

So basically if I have word wyz it will highlight all the words containing w or y or z.