Looking for similar value

[wyte32]

Hello all,

So I'm learning VBA also along with VBScript - and I'll be honest I keep getting the two confused, so please forgive me.
What I'm trying to do is: I have two columns (A & B), I'm trying to determine if column A has similar values that column B contains. So for example:

Column A	Animal Involved	Column C
The duck swam in the pond		Bear
The bear ate the fish		Duck
The bear danced		Monkey

I'd like to, in the Animal Involved column, be able to come up with a formula that will tell me if one of the values from column C is present in column A, and if so, what is it?

Is this possible? I have a script that I came up with, but it is in vbscript (sorry!) - after working with it for a while, I thought this might be easier in VBA and just call it as a macro. I've tried using =IF(MATCH(C2,A:A),"Animal Found","Animal Not Found"), but that didn't give me quite what I was looking for. Any suggestions?

[mancubus]

hi.
welcome to VBAX.


formula in B2, copy down.

=IF(COUNTIF(A:A,"*"&C2&"*")>0,"Animal Found","Animal Not Found")

[wyte32]

Thank you! Never occurred to me to use CountIF

Followup question: Would this be the most efficient way if column A had more rows than column b?
Like if we only had those three animals to choose from but we had like 5 more rows of sentences to
determine if an animal was there.

[mancubus]

you are welcome.

the copied range made me think the animal names were in column C.
now i understand they are in column B.


if that's the case, below formula in C2, copied down to corresponding non blank cell in column B, will work for you:

If(COUNTIF(A:A,"*"&B2&"*")>0,"Animal Found","Animal Not Found")

you can post your workbook here (click Go Advanced, scroll down to Manage Attachments) so that we can understand the table structure.

[wyte32]

Sentences	Is an Animal Involved?	Animals
The duck swam		Duck
The bear ate the fish		Bear
The bear danced		Monkey
Mike watched tv
The dog chased the cat
The duck quacked
The monkey rattled the cage
The snake hissed at me

Unable to upload at the moment, due to where I am. But pretty much that is my structure above.
I believe the formula you gave will work for now, but I forsee that column over to the left (Sentences)
growing. The formula would then need to check for any of the animals. So i tested the formula out, and then the results started showing incorrectly. I guess I'm unclear
as to how should I modify it when this happens.

[Bob Phillips]

Try this array formula

=SUM(IF(ISNUMBER(SEARCH($C$1:$C$4,$A2)),ROW($C$1:$C$4),0))>0

[wyte32]

xld, When I paste that formula in the cells in column B, it gives me the following output:

Sentences		Is an Animal Involved?	Animals
The duck swam		True	Duck
The bear ate the fish		True	Bear
The bear danced		False	Monkey
Mike watched tv		False
The dog chased the cat		False
The duck quacked		False
The monkey rattled the cage		False
The snake hissed at me	False

**not sure whats up with my table there lol....

Some of those are correct, but obviously, that's not the correct output I'm looking for. I think that's the problem I was having with the =IF(MATCH(C2,A:A),"Animal Found","Animal Not Found") formula in the beginning. But thank you for trying to help!

[JKwan]

xld gave you an array formula. You need to press Ctrl-Shift-Enter (not Enter key only), now do a drag fill down and your table will be correct.

[wyte32]

omg duh!! Thanks for pointing that out - works perfect now! Thanks all!