Related:
- Matches using a list of keywords
- Youtube channel keywords - Guide
- Counter strike 1.6 cheats list - Guide
- All contact number list with name - Guide
- How to change your best friends list on snapchat to 3 - Guide
- Amd crossfire compatibility list - Guide
4 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Nov 17, 2011 at 08:34 PM
Nov 17, 2011 at 08:34 PM
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
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
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?
I mean if my keywordlist contains mr it should be able to detect Mr.Penn from the list. Is this possible?
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Nov 18, 2011 at 05:10 AM
Nov 18, 2011 at 05:10 AM
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
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
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.
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.
