String in Cell A1:A100 comparing in Sentence cells B1:B200 marking the sentence Cell B if A:A exists in B

[diamanthian1]

Hi, I've been getting myself tied up in knots trying to do this with several loops but there must be a better way.


Cells A1 TO A100 contain a single word


Cells B2 to 200 contain a sentence


I am trying to find out if any word in Column A exists in the any sentence in Column B if True mark the sentence with a red background or something
If false do nothing


I'm struggling as I can find if the single word exists within the sentences with
=IF(ISERROR(MATCH(A100,B:B, 0)), " Match")


but I can't figure out how to mark the sentence


Any Help greatly appreciated

 

[JLGWhiz]

Hi, I've been getting myself tied up in knots trying to do this with several loops but there must be a better way.


Cells A1 TO A100 contain a single word


Cells B2 to 200 contain a sentence


I am trying to find out if any word in Column A exists in the any sentence in Column B if True mark the sentence with a red background or something
If false do nothing


I'm struggling as I can find if the single word exists within the sentences with
=IF(ISERROR(MATCH(A100,B:B, 0)), " Match")


but I can't figure out how to mark the sentence


Any Help greatly appreciated

If you don't want to use VBA then you would need to use conditional formating with a formula criteria and set your background color that way.

 

[diamanthian1]

Thanks for the reply,
The colour is not important just a flag to alert me that there is a match, this is only one step in a series but I've falled down here


I've found a workaround but it's not quite what I'm after I'm happy to use VBA but the only way I can think of is to use a set of nested loops that just seems overly resource consuming, the finished version will be running over 6000 in column A and 70,000 in Column B




Where list is a named list B:B200


=INDEX(list,IF(SUMPRODUCT(ISNUMBER(FIND(list,B1))*ROW($1:$100))<>0,SUMPRODUCT(ISNUMBER(FIND(list,B2))*ROW($1:$100)),NA()))


but the problem I have is that it is matching partial strings from instead of the full word found which is not very useful as you can imagine

 

[JLGWhiz]

Something like: Find(list," "&B1&" ")
Not sure I have that exactly right (poor on formulas) but the idea is to put a space on each side of the word and make that the value to look for instead of the trimmed word. Even if you use a partial match, it still has to find the space on each end also, so basically you are coercing it to look for the whole word and not a part of another word, but still embedded in a longer string of text.