PDA

View Full Version : Searching entire phrases for one or two words that contain many words



filla_dilla
06-08-2011, 01:04 PM
I have a list of phrases in rows A1:A72. In A76:A642, I also a list of phrases that aren't exact matches of what's in A1:A72. What I'm trying to do is highlight the words in A1:A72 if there's match from A76:642. For example, A1 contains:

BOB WENT TO THE GROCERY STORE TODAY

A76 contains:

WENT STORE

I need only WENT STORE highlighted in A1.

This process needs to be repeated throughout A1:A72 by using what's in A76:A642

p45cal
06-08-2011, 01:58 PM
Works on the active sheet:
Sub HighlightWords()
'Create a dictionary of all the words in A76:A642 :
Set Dict = CreateObject("Scripting.Dictionary")
Dict.CompareMode = vbTextCompare
For Each cll In Range("A76:A642").Cells
myArray = Split(Application.Trim(cll.Value), " ")
For Each word In myArray
If Not Dict.Exists(word) Then Dict.Add word, word
Next word
Next cll
'Now check each word in each cell in A1:A72 :
For Each cll In Range("A1:A72")
myArray = Split(Application.Trim(cll.Value), " ")
For Each word In myArray
If Dict.Exists(word) Then
'highlight word:
cll.Characters(Start:=InStr(1, cll.Value, word, vbTextCompare), Length:=Len(word)).Font.ColorIndex = 3
End If
Next word
Next cll
End Sub
but doesn't handle punctuation etc. - yet.

filla_dilla
06-08-2011, 02:27 PM
This is great! I've been looking all over for some kind of help and I really appreciate this!

p45cal
06-08-2011, 02:47 PM
Does it need to handle punctuation etc.?

filla_dilla
06-08-2011, 02:53 PM
It doesn't. The phrases in the lower A column don't have punctuation and I've added this for the top part of the column to take care of the punctuation that appears there:

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Dim c As Range
For Each c In Selection.Cells
c = Replace(c, "'", " ")
c = Replace(c, ",", "")
c = Replace(c, "-", " ")

Thanks again for your help, I would have never figured this out.

filla_dilla
06-09-2011, 10:03 AM
My apologies, but I wasn't clear in my original post.

I got to looking at this for some targeted testing and it's not working quite how I need it to. For example, I have (A1:A2):

THE DOG IS BIG
THE CAT IS SMALL

In A4:A5, I have:

BIG THE
CAT SMALL

I'm expecting THE BIG to be highlighted in A1 (which is the case) and CAT SMALL in A2, but THE is also highlighted in A2.

If there's more than one word in the lower section of column A, in order for the words to be highlighted in the upper part of column A, all of the words must exist in that string. So the only way that THE CAT SMALL can be highlighted in A2 is if those words are present in one row (lower section). Of course, if there's a single word in the lower part of A, then any occurance of that word in the upper part of A should continue to be highlighted.

Can you help me out again?

p45cal
06-09-2011, 11:29 AM
Which is quite different…
What happens if, lt's take your example, in the upper section we have:
THE DOG IS BIG
and in the lower section we have a three single word entries:
IS
BIG
DOG

and nothing similar, what would you want highlighting in the upper section?

Chabu
06-09-2011, 12:58 PM
And is the sequence important?
ie should BIG DOG be highlighted if DOG BIG is in the lower section?

filla_dilla
06-09-2011, 01:25 PM
Which is quite different…
What happens if, lt's take your example, in the upper section we have:
THE DOG IS BIG
and in the lower section we have a three single word entries:
IS
BIG
DOG

and nothing similar, what would you want highlighting in the upper section?

DOG IS BIG would be highlighted, but THE wouldn't be.

filla_dilla
06-09-2011, 01:27 PM
And is the sequence important?
ie should BIG DOG be highlighted if DOG BIG is in the lower section?

Sequence isn't important. More examples (upper section):

DOG IS BIG
CAT IS SMALL
CAR IS BIG
BIKE IS SMALL
HOUSE IS PURPLE
HOUSE IS GREEN

lower section:

BIG DOG
PURPLE
HOUSE GREEN

This would be highlighted:

DOG IS BIG
CAT IS SMALL
CAR IS BIG
BIKE IS SMALL
HOUSE IS PURPLE
HOUSE IS GREEN

p45cal
06-09-2011, 03:22 PM
Which is quite different…
What happens if, lt's take your example, in the upper section we have:
THE DOG IS BIG
and in the lower section we have a three single word entries:
IS
BIG
DOG

and nothing similar, what would you want highlighting in the upper section?

DOG IS BIG would be highlighted, but THE wouldn't be.

So, we have three words highlighted from 3 separate rows in the lower section.
What if, there were another row in the lower section which had
BIG DOG, now what would be highlighted in the same:
THE DOG IS BIG
my question being: would the IS be highlighted?

I think you'll say that the IS isn't highlighted.

If so, next scenario:
Upper section phrase:
BOB WENT TO THE GROCERY STORE TODAY

Lower section matches:
BOB WENT
GROCERY STORE
THE TODAY

what should be highlighted in the upper section phrase?

filla_dilla
06-09-2011, 04:04 PM
So, we have three words highlighted from 3 separate rows in the lower section.
What if, there were another row in the lower section which had
BIG DOG, now what would be highlighted in the same:
THE DOG IS BIG
my question being: would the IS be highlighted?

I think you'll say that the IS isn't highlighted.

If so, next scenario:
Upper section phrase:
BOB WENT TO THE GROCERY STORE TODAY

Lower section matches:
BOB WENT
GROCERY STORE
THE TODAY

what should be highlighted in the upper section phrase?

If BIG DOG is the only thing in the lower section, then you're correct, only DOG BIG would be highlighted. If we're still leaving in the initial three lower rows and adding BIG DOG, then DOG IS BIG would be highlighted. For the second question, words in bold would be highlighted:

BOB WENT TO THE GROCERY STORE TODAY

p45cal
06-09-2011, 04:17 PM
Understood. Sleep required. Tomorrow perhaps.

p45cal
06-10-2011, 03:53 AM
Note: made some corrections after initially posting.
Sub HighlightWords2()
For Each cll In Range("A1:A72")
'create an array of the words in cll:
'clean punctuation fist:
cllStr = UCase(cll.Value)
cllStr = Replace(cllStr, "'", " ")
cllStr = Replace(cllStr, ",", " ")
cllStr = Replace(cllStr, "'", " ")
cllList = Split(Application.Trim(cllStr), " ")
For Each cll2 In Range("A76:A642").Cells
cll2List = Split(Application.Trim(UCase(cll2.Value)), " ") 'assumes lower section has no puntuation.
'if all words in cll2List are in cllList then highlight those words in cll.
AllWordsFound = True ' (will set this to FALSE if any word not found)
For Each word2 In cll2List
WordFound = False '(will set this to TRUE if the word is found)
For Each word In cllList
If word = word2 Then
WordFound = True
Exit For 'no need to keep on looking so abort inner loop
End If
Next word
If Not WordFound Then
AllWordsFound = False
Exit For 'no need to keep on looking so abort outer loop
End If
Next word2
If AllWordsFound Then 'do the highlighting:
For Each word2 In cll2List
cll.Characters(Start:=InStr(1, cll.Value, word2, vbTextCompare), Length:=Len(word2)).Font.ColorIndex = 3
Next word2
End If
Next cll2
Next cll
End Sub

filla_dilla
06-10-2011, 08:55 AM
Note: made some corrections after initially posting.
Sub HighlightWords2()
For Each cll In Range("A1:A72")
'create an array of the words in cll:
'clean punctuation fist:
cllStr = UCase(cll.Value)
cllStr = Replace(cllStr, "'", " ")
cllStr = Replace(cllStr, ",", " ")
cllStr = Replace(cllStr, "'", " ")
cllList = Split(Application.Trim(cllStr), " ")
For Each cll2 In Range("A76:A642").Cells
cll2List = Split(Application.Trim(UCase(cll2.Value)), " ") 'assumes lower section has no puntuation.
'if all words in cll2List are in cllList then highlight those words in cll.
AllWordsFound = True ' (will set this to FALSE if any word not found)
For Each word2 In cll2List
WordFound = False '(will set this to TRUE if the word is found)
For Each word In cllList
If word = word2 Then
WordFound = True
Exit For 'no need to keep on looking so abort inner loop
End If
Next word
If Not WordFound Then
AllWordsFound = False
Exit For 'no need to keep on looking so abort outer loop
End If
Next word2
If AllWordsFound Then 'do the highlighting:
For Each word2 In cll2List
cll.Characters(Start:=InStr(1, cll.Value, word2, vbTextCompare), Length:=Len(word2)).Font.ColorIndex = 3
Next word2
End If
Next cll2
Next cll
End Sub


By golly, you did it! Thank you so much for all of your time working on this!:beerchug:

Chabu
06-10-2011, 02:39 PM
I don't think it works as it should

filla_dilla
06-10-2011, 03:14 PM
I don't think it works as it should

Why's that? I tested it and it works as I need it to. p45cal did a great job from my perspective.

p45cal
06-10-2011, 03:14 PM
I don't think it works as it should

…and?

(this means a bit of elaboration might be helpful)

Chabu
06-11-2011, 03:30 AM
I tried it and it works for the first line in the upper range, not for subsequent lines

And even for the first line it does not find the AND nor the I
(I changed the two ranges to A1:A9 and A10:A19)
http://www.vbaexpress.com/forum/data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAXsAAAEACAIAAADduUFeAAAPvUlEQVR4nO2c0Xar OhIF/f9fxZ95Hu4djwNSq1vC2nRP1ToPDiCxlWwqHIfkdQAA7OJ1HMcbAOD3YBwA2AfGAYB9XIzzep3/nejtGg48T3M+wNjy+ot/znb+5pZr/s+RE0NasU/hxyvqzW8sahSpO799XufA017PWuzTeb58zUk8n42J5Xs+vbectLfGiS0Po2WcE0YD5srxXzWcrr TrtfdtnOb25rRWhuYV27uMvw8wFm6P7QRzrsh7uu9rzMn0l9L/qbDnnDjd9KUYOuCWT+/iSXtjQ1+aRxIxTu8b2nBgY1DbOKeNPeM0t/Q2Nr4kzS/S8JII9cMMFgjvPF30kpj+Ut47cDjKeO2fYfjhaeOPjLN4+WCc8V6M0wn2UONMzz+nFc/pvj+cm9yYsHdMLuPYu55H5H2c4aesN/A8ouGU79fGi+ZAe2PAOL0lGC+aH46C3WAcI6rnCyExzvDT2wvguaLs1t1oHHfP7zSOfRmmN86J6 bZ1PgW7jfNufUkM9fSGN18Mx7aCPesex6Mt49TOs4dkFBrenM0ePlxFaPlGktBJjeH+L8Hj2W0c 48c3V/s8zjjNgfbYVrBnGceeZPGysQcOT+cZ7jl+ehXBL/Q9J73uGkoZ4/SON9yBcVwBmqf7nXHejs+S87Jxhpn4Hh69qbl3+c5UN37q/l+Mc91rfzF8mvhsN35YPv/TcX+80CWxZpzrlkf/dHz6wwmh94681zj2AT8yztxJ7W+ic8GkbH0CMGSc7y3N/4X55/83nr3lmt//TSZunPffRdnDY8ZxfCEaBzvntwee9vaGRD+9zS3GWpzDJ5Yf/fSGTtrcVd84AAA/A+MAwD4wDgDs41/jAADs4XVwjwNusreF/FowDsTI3hbya8E4ECN7W8ivpWscz2O+/idomk+d+Lc4fzfCOeT7yOGcvRnstTsXOHwkx15Fb7i9ame25pZ3icarIyxRIL/LOJ/L+LSxedj1yCbOU9izGRmGSYZXo0crxi47mz+nfbqJmXufVVup/1Cg8eoISxTIv2Scd6emHuM0x0ZnM0Y5xxrb143Ty9Y8tT+qPdypJ4yTkQL5MU53e2jsunEM9hjH 3vUPBRqvjrBEgfxj4wxf9/5DccJzlt78vdmMF80PT7ua8U6vm+HnIj3BOIad362vwokCjVdHWKJA/l8Zx5mgJxr7wvBk8I/1bDesNExih7Sl3Dzd74zT2/ihQOPVEZYokH9gHM838/dFE/uN0xzoHxsdEjLONVv0Ur/LOMZX0xPjXaLx6ghLFMj/P+PY9y/2rpLGca69t+uZxhmeFOM8mQL5LePY1+T1MpgwTvPgaWtcv2k/xDjXbP6c9umuH/Z2Oe/pMM6TKZD/z/+qTnfaIeO8/5Z7ePd+HdXbYsx2o62a23tLmDCOva5eSCNqb7g/cy+VkaRA49URliiQn99ygADZ20J+LRgHYmRvC/m1YByIkb0t5Ndy8Be5AGAnryO5NWEn2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8LxoEY2 dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEg Rva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF/LwV/kAoCdvI7k1oSdZG8L+bVgHIiRvS3k14JxIEb2tpBfC8aBGNnbQn4tGAdiZG8L+bVgHIiRvS3k14 JxIEb2tpBfC8aBGNnbQn4tGAdiZG8L+bVgHIiRvS3k14JxIEb2tpBfC8aBGNnbQn4tGAdiZG8L+ bVgHIiRvS3k14JxIEb2tpBfC8aBGNnbQn4tB3+RCwB28jqSWxN2kr0t5NeCcSBG9raQXwvGgRjZ 20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1HPxFLgDYyetIbk3YSfa2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8LxoEY2 dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEg Rva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXcvAXuQBgJ68juTVhJ9nbQn4tGAdiZG8L+bV gHIiRvS3k14JxIEb2tpBfC8aBGNnbQn4tGAdiZG8L+bVgHIiRvS3k14JxIEb2tpBfC8aBGNnbQn 4tGAdiZG8L+bVgHIiRvS3k14JxIEb2tpBfC8aBGNnbQn4tGAdiZG8L+bVgHIiRvS3k14JxIEb2t pBfy8Ff5AKAnbyO5NaEnWRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LQd/kQsAdvI6klsTdpK9LeTXgnEgRva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8 LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9Le TXgnEgRva2kF8LxoEY2dtCfi0YB2Jkbwv5tRz8RS4A2MnrSG5N2En2tpBfC8aBGNnbQn4tGAdiZ G8L+bWcjfO6cBrQ2zUceJ3Hv8U5c3OXvdHYe12RvajQippJop9DCQUar46wRIH8Z+OcjvjecrWM c2BzV9NZzRmM8w63R41jy8igGXJ6jY+lQOPVEZYokN9rnOlLtzdqeIlGJ183ju1QG6dxemfBOHs gvxaM47qD84BxUkB+LYH3cYaXrvM9iN6c12tvp3Gap/AvqreW3uvmi9DpVBRovDrCEgXy777HeaBxmpf3yj3OLWt8JgUar46wRIH8W41jfCe/Xpl7jNM7y7Rx7lrjMynQeHWEJQrk320czww7jdM72KMA+/7F3oVxJJBfy9afjtv/z7pOHr37WDeOvcuevLkKe6+xxsdSoPHqCEsUyL/vCcDQ1fj+e/HbMxvzN4PZ1/nnGOeiPOKIrtFeqZACjVdHWKJAfn7LAQJkbwv5tWAciJG9LeTXgnEgRva2kF/LwV/kAoCdvI7k1oSdZG8L+bVgHIiRvS3k14JxIEb2tpBfS9s40w/dhB5d6c1pPKLyPeo6eXOFzplPk0eXb09YiQKNV0dYokD+s3Gul1lvl73XOY9xZfpPZ+jG3hI6wD i4ql+uFGi8OsISBfL/MU7zyjGuK/uSmx44HGW89s8w/HAYFeOkg/xaXMYx9k4bx3NGpy/mJjcm7B2Dcd4lGq+OsESB/Hrj9N4BWTfO9/y9vcaHw1V87+J9nBSQX4tlnKYCehfVj+5xhtew/8JuzhA1Tmj5JSnQeHWEJQrkn3wfZ/HuwB44PJ1nuOf46VV4BpakQOPVEZYokH/+neOVe5wJGU3c40Rvau5dfkkKNF4dYYkC+ed/Oj79ofNa3W8c+wCM8y7ReHWEJQrkjz0BeD3SM/C0tzfkdMDwdM0txlqcwyeWb6yiGAUar46wRIH8/JYDBMjeFvJrwTgQI3tbyK8F40CM7G0hv5aDv8gFADt5HcmtCTvJ3hbya8E4ECN7W8ivBeNAjOxt Ib+WP8bpPVSy+ByKfYzx8Mvw2RbP8zVVn4tRUaDx6ghLFMjfuMcJPfnmP9lQBycTecbac6Kb2yn QeHWEJQrklxmnOdaYuTkc42ymQOPVEZYokL+acexdsEiBxqsjLFEgv8s4vTdiPO/jGNMax/hvXvz/4YJ1CjReHWGJAvlT3uMM/+uEbn5EgcarIyxRIH8+43hurDDOjyjQeHWEJQrkT2kce+ZoMPBToPHqCEsUyJ/sp+O9M3p0BusUaLw6whIF8q8aZ/qd4+sMve3Dd208meEWCjReHWGJAvn5LQcIkL0t5NeCcSBG9raQXwvGgRjZ20J+LQd/kQsAdvI6klsTdpK9LeTXgnEgRva2kF8LxoEY2dtCfi1n48w9zvv5zcneszkTQz6HGQfYDwF5HhG CKAUar46wRIH8MeN8TDHceDrAfkJvKAVnsOn5wUmBxqsjLFEg/8A4p41XuRg3Mp5Rw7H+4+0kSOcWCjReHWGJAvkxDgQo0Hh1hCUK5L/TOL33XIwXxnmNAzCOigKNV0dYokD+sXHefdEYty3X4c0Xw7H+4zHOBgo0Xh1hiQL5txqnOdAe2z wA46go0Hh1hCUK5Mc4EKBA49URliiQ32Wc5i6/Na5H8tPxpBRovDrCEgXy32mc4TvHxlg7aO+25XQi/16Yo0Dj1RGWKJCf33KAANnbQn4tGAdiZG8L+bVgHIiRvS3k13LwF7kAYCevI7k1YSfZ20J+LRgH YmRvC/m1YByIkb0t5NdyNk7vgZrmAb3tnkdghk/ovP8+Q+yZ2f+osefpZOPxIufZV9b42MeICjReHWGJAvnPxjkd8b3lahnnwOauprOaMxjnHW6PGs eWkUEz5PQaH0uBxqsjLFEgv9c405dub9TwEo1Ovm4c26E2TuP0zoJx9kB+LRjHdQfnAeOkgPxaA u/jDC9d53sQvTmv195O4zRP4V9Uby29180XodOpKNB4dYQlCuTffY/zQOM0L++Ve5xb1vhMCjReHWGJAvm3Gsf4Tn69MvcYp3eWaePctcZnUqDx6ghLFMi/2zieGXYap3ewRwH2/Yu9C+NIIL+WrT8dt/+fdZ08evexbhx7lz15cxX2XmONj6VA49URliiQf98TgKGr8f334rdnNuZvBrOv888xzkV5xBFdo 71SIQUar46wRIH8/JYDBMjeFvJrwTgQI3tbyK8F40CM7G0hv5aDv8gFADt5HcmtCTvJ3hbya8E4ECN7W8ivBeNAjOxt Ib+Ws3E2P4/T22I8nDIxBG6kQOPVEZYokP9snNMR31uulnEONIZ7Np4OMGLYY2GdAo1XR1iiQH6vcezbFo9x3i 1JNbVlG6c3ajgW1inQeHWEJQrkxzgQoEDj1RGWKJDf+z7O0Die91D8xhm+j9PTE8b5KQUar46wR IH8W+9x3n3RDO3WOyPG2UmBxqsjLFEgf1bjNAfaY2GdAo1XR1iiQH6MAwEKNF4dYYkC+ff9dNx/sH/s9T0jjPNTCjReHWGJAvn3PQH4fbC9xfPOsTHWODUsUqDx6ghLFMjPbzlAgOxtIb8WjAMxsreF/FowDsTI3hbyazn4i1wAsJP/ADJkV7wyYY5rAAAAAElFTkSuQmCC

Chabu
06-11-2011, 03:39 AM
I tried to put a picture in but it did not work
These are the test lines

BOB AND I WENT TO THE GROCERY STORE TODAY
JANE WENT FISHING TODAY



BOB AND I LIKE JANE
GROCERY STORE
THE TODAY
I WENT TOO
BOB AND I LIKE JANE
BOB WENT

So the first two lines are in the first range, the only words coloured for me are "BOB WENT THE GROCERY STORE TODAY" on the fist line, not the AND nor the I
And in the second line nothing was highlighted


http://www.vbaexpress.com/forum/data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAXsAAAEACAIAAADduUFeAAAPvUlEQVR4nO2c0Xar OhIF/f9fxZ95Hu4djwNSq1vC2nRP1ToPDiCxlWwqHIfkdQAA7OJ1HMcbAOD3YBwA2AfGAYB9XIzzep3/nejtGg48T3M+wNjy+ot/znb+5pZr/s+RE0NasU/hxyvqzW8sahSpO799XufA017PWuzTeb58zUk8n42J5Xs+vbectLfGiS0Po2WcE0YD5srxXzWcrr TrtfdtnOb25rRWhuYV27uMvw8wFm6P7QRzrsh7uu9rzMn0l9L/qbDnnDjd9KUYOuCWT+/iSXtjQ1+aRxIxTu8b2nBgY1DbOKeNPeM0t/Q2Nr4kzS/S8JII9cMMFgjvPF30kpj+Ut47cDjKeO2fYfjhaeOPjLN4+WCc8V6M0wn2UONMzz+nFc/pvj+cm9yYsHdMLuPYu55H5H2c4aesN/A8ouGU79fGi+ZAe2PAOL0lGC+aH46C3WAcI6rnCyExzvDT2wvguaLs1t1oHHfP7zSOfRmmN86J6 bZ1PgW7jfNufUkM9fSGN18Mx7aCPesex6Mt49TOs4dkFBrenM0ePlxFaPlGktBJjeH+L8Hj2W0c 48c3V/s8zjjNgfbYVrBnGceeZPGysQcOT+cZ7jl+ehXBL/Q9J73uGkoZ4/SON9yBcVwBmqf7nXHejs+S87Jxhpn4Hh69qbl3+c5UN37q/l+Mc91rfzF8mvhsN35YPv/TcX+80CWxZpzrlkf/dHz6wwmh94681zj2AT8yztxJ7W+ic8GkbH0CMGSc7y3N/4X55/83nr3lmt//TSZunPffRdnDY8ZxfCEaBzvntwee9vaGRD+9zS3GWpzDJ5Yf/fSGTtrcVd84AAA/A+MAwD4wDgDs41/jAADs4XVwjwNusreF/FowDsTI3hbya8E4ECN7W8ivpWscz2O+/idomk+d+Lc4fzfCOeT7yOGcvRnstTsXOHwkx15Fb7i9ame25pZ3icarIyxRIL/LOJ/L+LSxedj1yCbOU9izGRmGSYZXo0crxi47mz+nfbqJmXufVVup/1Cg8eoISxTIv2Scd6emHuM0x0ZnM0Y5xxrb143Ty9Y8tT+qPdypJ4yTkQL5MU53e2jsunEM9hjH 3vUPBRqvjrBEgfxj4wxf9/5DccJzlt78vdmMF80PT7ua8U6vm+HnIj3BOIad362vwokCjVdHWKJA/l8Zx5mgJxr7wvBk8I/1bDesNExih7Sl3Dzd74zT2/ihQOPVEZYokH9gHM838/dFE/uN0xzoHxsdEjLONVv0Ur/LOMZX0xPjXaLx6ghLFMj/P+PY9y/2rpLGca69t+uZxhmeFOM8mQL5LePY1+T1MpgwTvPgaWtcv2k/xDjXbP6c9umuH/Z2Oe/pMM6TKZD/z/+qTnfaIeO8/5Z7ePd+HdXbYsx2o62a23tLmDCOva5eSCNqb7g/cy+VkaRA49URliiQn99ygADZ20J+LRgHYmRvC/m1YByIkb0t5Ndy8Be5AGAnryO5NWEn2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8LxoEY2 dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEg Rva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF/LwV/kAoCdvI7k1oSdZG8L+bVgHIiRvS3k14JxIEb2tpBfC8aBGNnbQn4tGAdiZG8L+bVgHIiRvS3k14 JxIEb2tpBfC8aBGNnbQn4tGAdiZG8L+bVgHIiRvS3k14JxIEb2tpBfC8aBGNnbQn4tGAdiZG8L+ bVgHIiRvS3k14JxIEb2tpBfC8aBGNnbQn4tB3+RCwB28jqSWxN2kr0t5NeCcSBG9raQXwvGgRjZ 20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1HPxFLgDYyetIbk3YSfa2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8LxoEY2 dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEg Rva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXcvAXuQBgJ68juTVhJ9nbQn4tGAdiZG8L+bV gHIiRvS3k14JxIEb2tpBfC8aBGNnbQn4tGAdiZG8L+bVgHIiRvS3k14JxIEb2tpBfC8aBGNnbQn 4tGAdiZG8L+bVgHIiRvS3k14JxIEb2tpBfC8aBGNnbQn4tGAdiZG8L+bVgHIiRvS3k14JxIEb2t pBfy8Ff5AKAnbyO5NaEnWRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LQd/kQsAdvI6klsTdpK9LeTXgnEgRva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8 LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9Le TXgnEgRva2kF8LxoEY2dtCfi0YB2Jkbwv5tRz8RS4A2MnrSG5N2En2tpBfC8aBGNnbQn4tGAdiZ G8L+bWcjfO6cBrQ2zUceJ3Hv8U5c3OXvdHYe12RvajQippJop9DCQUar46wRIH8Z+OcjvjecrWM c2BzV9NZzRmM8w63R41jy8igGXJ6jY+lQOPVEZYokN9rnOlLtzdqeIlGJ183ju1QG6dxemfBOHs gvxaM47qD84BxUkB+LYH3cYaXrvM9iN6c12tvp3Gap/AvqreW3uvmi9DpVBRovDrCEgXy777HeaBxmpf3yj3OLWt8JgUar46wRIH8W41jfCe/Xpl7jNM7y7Rx7lrjMynQeHWEJQrk320czww7jdM72KMA+/7F3oVxJJBfy9afjtv/z7pOHr37WDeOvcuevLkKe6+xxsdSoPHqCEsUyL/vCcDQ1fj+e/HbMxvzN4PZ1/nnGOeiPOKIrtFeqZACjVdHWKJAfn7LAQJkbwv5tWAciJG9LeTXgnEgRva2kF/LwV/kAoCdvI7k1oSdZG8L+bVgHIiRvS3k14JxIEb2tpBfS9s40w/dhB5d6c1pPKLyPeo6eXOFzplPk0eXb09YiQKNV0dYokD+s3Gul1lvl73XOY9xZfpPZ+jG3hI6wD i4ql+uFGi8OsISBfL/MU7zyjGuK/uSmx44HGW89s8w/HAYFeOkg/xaXMYx9k4bx3NGpy/mJjcm7B2Dcd4lGq+OsESB/Hrj9N4BWTfO9/y9vcaHw1V87+J9nBSQX4tlnKYCehfVj+5xhtew/8JuzhA1Tmj5JSnQeHWEJQrkn3wfZ/HuwB44PJ1nuOf46VV4BpakQOPVEZYokH/+neOVe5wJGU3c40Rvau5dfkkKNF4dYYkC+ed/Oj79ofNa3W8c+wCM8y7ReHWEJQrkjz0BeD3SM/C0tzfkdMDwdM0txlqcwyeWb6yiGAUar46wRIH8/JYDBMjeFvJrwTgQI3tbyK8F40CM7G0hv5aDv8gFADt5HcmtCTvJ3hbya8E4ECN7W8ivBeNAjOxt Ib+WP8bpPVSy+ByKfYzx8Mvw2RbP8zVVn4tRUaDx6ghLFMjfuMcJPfnmP9lQBycTecbac6Kb2yn QeHWEJQrklxmnOdaYuTkc42ymQOPVEZYokL+acexdsEiBxqsjLFEgv8s4vTdiPO/jGNMax/hvXvz/4YJ1CjReHWGJAvlT3uMM/+uEbn5EgcarIyxRIH8+43hurDDOjyjQeHWEJQrkT2kce+ZoMPBToPHqCEsUyJ/sp+O9M3p0BusUaLw6whIF8q8aZ/qd4+sMve3Dd208meEWCjReHWGJAvn5LQcIkL0t5NeCcSBG9raQXwvGgRjZ20J+LQd/kQsAdvI6klsTdpK9LeTXgnEgRva2kF8LxoEY2dtCfi1n48w9zvv5zcneszkTQz6HGQfYDwF5HhG CKAUar46wRIH8MeN8TDHceDrAfkJvKAVnsOn5wUmBxqsjLFEg/8A4p41XuRg3Mp5Rw7H+4+0kSOcWCjReHWGJAvkxDgQo0Hh1hCUK5L/TOL33XIwXxnmNAzCOigKNV0dYokD+sXHefdEYty3X4c0Xw7H+4zHOBgo0Xh1hiQL5txqnOdAe2z wA46go0Hh1hCUK5Mc4EKBA49URliiQ32Wc5i6/Na5H8tPxpBRovDrCEgXy32mc4TvHxlg7aO+25XQi/16Yo0Dj1RGWKJCf33KAANnbQn4tGAdiZG8L+bVgHIiRvS3k13LwF7kAYCevI7k1YSfZ20J+LRgH YmRvC/m1YByIkb0t5NdyNk7vgZrmAb3tnkdghk/ovP8+Q+yZ2f+osefpZOPxIufZV9b42MeICjReHWGJAvnPxjkd8b3lahnnwOauprOaMxjnHW6PGs eWkUEz5PQaH0uBxqsjLFEgv9c405dub9TwEo1Ovm4c26E2TuP0zoJx9kB+LRjHdQfnAeOkgPxaA u/jDC9d53sQvTmv195O4zRP4V9Uby29180XodOpKNB4dYQlCuTffY/zQOM0L++Ve5xb1vhMCjReHWGJAvm3Gsf4Tn69MvcYp3eWaePctcZnUqDx6ghLFMi/2zieGXYap3ewRwH2/Yu9C+NIIL+WrT8dt/+fdZ08evexbhx7lz15cxX2XmONj6VA49URliiQf98TgKGr8f334rdnNuZvBrOv888xzkV5xBFdo 71SIQUar46wRIH8/JYDBMjeFvJrwTgQI3tbyK8F40CM7G0hv5aDv8gFADt5HcmtCTvJ3hbya8E4ECN7W8ivBeNAjOxt Ib+Ws3E2P4/T22I8nDIxBG6kQOPVEZYokP9snNMR31uulnEONIZ7Np4OMGLYY2GdAo1XR1iiQH6vcezbFo9x3i 1JNbVlG6c3ajgW1inQeHWEJQrkxzgQoEDj1RGWKJDf+z7O0Die91D8xhm+j9PTE8b5KQUar46wR IH8W+9x3n3RDO3WOyPG2UmBxqsjLFEgf1bjNAfaY2GdAo1XR1iiQH6MAwEKNF4dYYkC+ff9dNx/sH/s9T0jjPNTCjReHWGJAvn3PQH4fbC9xfPOsTHWODUsUqDx6ghLFMjPbzlAgOxtIb8WjAMxsreF/FowDsTI3hbyazn4i1wAsJP/ADJkV7wyYY5rAAAAAElFTkSuQmCChttp://www.vbaexpress.com/forum/data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAXsAAAEACAIAAADduUFeAAAPvUlEQVR4nO2c0Xar OhIF/f9fxZ95Hu4djwNSq1vC2nRP1ToPDiCxlWwqHIfkdQAA7OJ1HMcbAOD3YBwA2AfGAYB9XIzzep3/nejtGg48T3M+wNjy+ot/znb+5pZr/s+RE0NasU/hxyvqzW8sahSpO799XufA017PWuzTeb58zUk8n42J5Xs+vbectLfGiS0Po2WcE0YD5srxXzWcrr TrtfdtnOb25rRWhuYV27uMvw8wFm6P7QRzrsh7uu9rzMn0l9L/qbDnnDjd9KUYOuCWT+/iSXtjQ1+aRxIxTu8b2nBgY1DbOKeNPeM0t/Q2Nr4kzS/S8JII9cMMFgjvPF30kpj+Ut47cDjKeO2fYfjhaeOPjLN4+WCc8V6M0wn2UONMzz+nFc/pvj+cm9yYsHdMLuPYu55H5H2c4aesN/A8ouGU79fGi+ZAe2PAOL0lGC+aH46C3WAcI6rnCyExzvDT2wvguaLs1t1oHHfP7zSOfRmmN86J6 bZ1PgW7jfNufUkM9fSGN18Mx7aCPesex6Mt49TOs4dkFBrenM0ePlxFaPlGktBJjeH+L8Hj2W0c 48c3V/s8zjjNgfbYVrBnGceeZPGysQcOT+cZ7jl+ehXBL/Q9J73uGkoZ4/SON9yBcVwBmqf7nXHejs+S87Jxhpn4Hh69qbl3+c5UN37q/l+Mc91rfzF8mvhsN35YPv/TcX+80CWxZpzrlkf/dHz6wwmh94681zj2AT8yztxJ7W+ic8GkbH0CMGSc7y3N/4X55/83nr3lmt//TSZunPffRdnDY8ZxfCEaBzvntwee9vaGRD+9zS3GWpzDJ5Yf/fSGTtrcVd84AAA/A+MAwD4wDgDs41/jAADs4XVwjwNusreF/FowDsTI3hbya8E4ECN7W8ivpWscz2O+/idomk+d+Lc4fzfCOeT7yOGcvRnstTsXOHwkx15Fb7i9ame25pZ3icarIyxRIL/LOJ/L+LSxedj1yCbOU9izGRmGSYZXo0crxi47mz+nfbqJmXufVVup/1Cg8eoISxTIv2Scd6emHuM0x0ZnM0Y5xxrb143Ty9Y8tT+qPdypJ4yTkQL5MU53e2jsunEM9hjH 3vUPBRqvjrBEgfxj4wxf9/5DccJzlt78vdmMF80PT7ua8U6vm+HnIj3BOIad362vwokCjVdHWKJA/l8Zx5mgJxr7wvBk8I/1bDesNExih7Sl3Dzd74zT2/ihQOPVEZYokH9gHM838/dFE/uN0xzoHxsdEjLONVv0Ur/LOMZX0xPjXaLx6ghLFMj/P+PY9y/2rpLGca69t+uZxhmeFOM8mQL5LePY1+T1MpgwTvPgaWtcv2k/xDjXbP6c9umuH/Z2Oe/pMM6TKZD/z/+qTnfaIeO8/5Z7ePd+HdXbYsx2o62a23tLmDCOva5eSCNqb7g/cy+VkaRA49URliiQn99ygADZ20J+LRgHYmRvC/m1YByIkb0t5Ndy8Be5AGAnryO5NWEn2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8LxoEY2 dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEg Rva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF/LwV/kAoCdvI7k1oSdZG8L+bVgHIiRvS3k14JxIEb2tpBfC8aBGNnbQn4tGAdiZG8L+bVgHIiRvS3k14 JxIEb2tpBfC8aBGNnbQn4tGAdiZG8L+bVgHIiRvS3k14JxIEb2tpBfC8aBGNnbQn4tGAdiZG8L+ bVgHIiRvS3k14JxIEb2tpBfC8aBGNnbQn4tB3+RCwB28jqSWxN2kr0t5NeCcSBG9raQXwvGgRjZ 20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1HPxFLgDYyetIbk3YSfa2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8LxoEY2 dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEg Rva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXcvAXuQBgJ68juTVhJ9nbQn4tGAdiZG8L+bV gHIiRvS3k14JxIEb2tpBfC8aBGNnbQn4tGAdiZG8L+bVgHIiRvS3k14JxIEb2tpBfC8aBGNnbQn 4tGAdiZG8L+bVgHIiRvS3k14JxIEb2tpBfC8aBGNnbQn4tGAdiZG8L+bVgHIiRvS3k14JxIEb2t pBfy8Ff5AKAnbyO5NaEnWRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LRgHYmRvC/m1YByIkb0t5NeCcSBG9raQXwvGgRjZ20J+LQd/kQsAdvI6klsTdpK9LeTXgnEgRva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8 LxoEY2dtCfi0YB2Jkbwv5tWAciJG9LeTXgnEgRva2kF8LxoEY2dtCfi0YB2Jkbwv5tWAciJG9Le TXgnEgRva2kF8LxoEY2dtCfi0YB2Jkbwv5tRz8RS4A2MnrSG5N2En2tpBfC8aBGNnbQn4tGAdiZ G8L+bWcjfO6cBrQ2zUceJ3Hv8U5c3OXvdHYe12RvajQippJop9DCQUar46wRIH8Z+OcjvjecrWM c2BzV9NZzRmM8w63R41jy8igGXJ6jY+lQOPVEZYokN9rnOlLtzdqeIlGJ183ju1QG6dxemfBOHs gvxaM47qD84BxUkB+LYH3cYaXrvM9iN6c12tvp3Gap/AvqreW3uvmi9DpVBRovDrCEgXy777HeaBxmpf3yj3OLWt8JgUar46wRIH8W41jfCe/Xpl7jNM7y7Rx7lrjMynQeHWEJQrk320czww7jdM72KMA+/7F3oVxJJBfy9afjtv/z7pOHr37WDeOvcuevLkKe6+xxsdSoPHqCEsUyL/vCcDQ1fj+e/HbMxvzN4PZ1/nnGOeiPOKIrtFeqZACjVdHWKJAfn7LAQJkbwv5tWAciJG9LeTXgnEgRva2kF/LwV/kAoCdvI7k1oSdZG8L+bVgHIiRvS3k14JxIEb2tpBfS9s40w/dhB5d6c1pPKLyPeo6eXOFzplPk0eXb09YiQKNV0dYokD+s3Gul1lvl73XOY9xZfpPZ+jG3hI6wD i4ql+uFGi8OsISBfL/MU7zyjGuK/uSmx44HGW89s8w/HAYFeOkg/xaXMYx9k4bx3NGpy/mJjcm7B2Dcd4lGq+OsESB/Hrj9N4BWTfO9/y9vcaHw1V87+J9nBSQX4tlnKYCehfVj+5xhtew/8JuzhA1Tmj5JSnQeHWEJQrkn3wfZ/HuwB44PJ1nuOf46VV4BpakQOPVEZYokH/+neOVe5wJGU3c40Rvau5dfkkKNF4dYYkC+ed/Oj79ofNa3W8c+wCM8y7ReHWEJQrkjz0BeD3SM/C0tzfkdMDwdM0txlqcwyeWb6yiGAUar46wRIH8/JYDBMjeFvJrwTgQI3tbyK8F40CM7G0hv5aDv8gFADt5HcmtCTvJ3hbya8E4ECN7W8ivBeNAjOxt Ib+WP8bpPVSy+ByKfYzx8Mvw2RbP8zVVn4tRUaDx6ghLFMjfuMcJPfnmP9lQBycTecbac6Kb2yn QeHWEJQrklxmnOdaYuTkc42ymQOPVEZYokL+acexdsEiBxqsjLFEgv8s4vTdiPO/jGNMax/hvXvz/4YJ1CjReHWGJAvlT3uMM/+uEbn5EgcarIyxRIH8+43hurDDOjyjQeHWEJQrkT2kce+ZoMPBToPHqCEsUyJ/sp+O9M3p0BusUaLw6whIF8q8aZ/qd4+sMve3Dd208meEWCjReHWGJAvn5LQcIkL0t5NeCcSBG9raQXwvGgRjZ20J+LQd/kQsAdvI6klsTdpK9LeTXgnEgRva2kF8LxoEY2dtCfi1n48w9zvv5zcneszkTQz6HGQfYDwF5HhG CKAUar46wRIH8MeN8TDHceDrAfkJvKAVnsOn5wUmBxqsjLFEg/8A4p41XuRg3Mp5Rw7H+4+0kSOcWCjReHWGJAvkxDgQo0Hh1hCUK5L/TOL33XIwXxnmNAzCOigKNV0dYokD+sXHefdEYty3X4c0Xw7H+4zHOBgo0Xh1hiQL5txqnOdAe2z wA46go0Hh1hCUK5Mc4EKBA49URliiQ32Wc5i6/Na5H8tPxpBRovDrCEgXy32mc4TvHxlg7aO+25XQi/16Yo0Dj1RGWKJCf33KAANnbQn4tGAdiZG8L+bVgHIiRvS3k13LwF7kAYCevI7k1YSfZ20J+LRgH YmRvC/m1YByIkb0t5NdyNk7vgZrmAb3tnkdghk/ovP8+Q+yZ2f+osefpZOPxIufZV9b42MeICjReHWGJAvnPxjkd8b3lahnnwOauprOaMxjnHW6PGs eWkUEz5PQaH0uBxqsjLFEgv9c405dub9TwEo1Ovm4c26E2TuP0zoJx9kB+LRjHdQfnAeOkgPxaA u/jDC9d53sQvTmv195O4zRP4V9Uby29180XodOpKNB4dYQlCuTffY/zQOM0L++Ve5xb1vhMCjReHWGJAvm3Gsf4Tn69MvcYp3eWaePctcZnUqDx6ghLFMi/2zieGXYap3ewRwH2/Yu9C+NIIL+WrT8dt/+fdZ08evexbhx7lz15cxX2XmONj6VA49URliiQf98TgKGr8f334rdnNuZvBrOv888xzkV5xBFdo 71SIQUar46wRIH8/JYDBMjeFvJrwTgQI3tbyK8F40CM7G0hv5aDv8gFADt5HcmtCTvJ3hbya8E4ECN7W8ivBeNAjOxt Ib+Ws3E2P4/T22I8nDIxBG6kQOPVEZYokP9snNMR31uulnEONIZ7Np4OMGLYY2GdAo1XR1iiQH6vcezbFo9x3i 1JNbVlG6c3ajgW1inQeHWEJQrkxzgQoEDj1RGWKJDf+z7O0Die91D8xhm+j9PTE8b5KQUar46wR IH8W+9x3n3RDO3WOyPG2UmBxqsjLFEgf1bjNAfaY2GdAo1XR1iiQH6MAwEKNF4dYYkC+ff9dNx/sH/s9T0jjPNTCjReHWGJAvn3PQH4fbC9xfPOsTHWODUsUqDx6ghLFMjPbzlAgOxtIb8WjAMxsreF/FowDsTI3hbyazn4i1wAsJP/ADJkV7wyYY5rAAAAAElFTkSuQmCC

Chabu
06-11-2011, 03:51 AM
here is the image of my result

(possibly it is just me that did not get the requirements right) :doh:

p45cal
06-11-2011, 05:12 AM
Chabu,
From msg#6: "all of the words must exist in that string"
6114
Where the picture reads "Are ALL the words in row 1 present?" more clearly:
"Are all these words present in row 1?"
Row 2 contains no matches.

Did my first attempt (msg#2) give what you expected?:
BOB AND I WENT TO THE GROCERY STORE TODAY
JANE WENT FISHING TODAY
http://www.box.net/shared/71997xh4187hv6lvod69

mancubus
06-11-2011, 05:17 AM
@CHABU

for A1 (BOB / WENT / THE / GROCERY / STORE / TODAY are highlighted) in your case:

HIGHLIGTED : MATCH
GROCERY / STORE : GROCERY STORE (A11)
THE / TODAY : THE TODAY (A12)
BOB / WENT : BOB WENT (A15)

they are highlighted because "all cell content" found in A1 (word order not important) and not in A2.

Chabu
06-11-2011, 02:31 PM
I see, I missed that.
Thanks for the explanation!