PDA

View Full Version : [SOLVED] Looking for similar value



wyte32
06-17-2014, 05:04 PM
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
06-18-2014, 04:41 AM
hi.
welcome to VBAX.


formula in B2, copy down.


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

wyte32
06-18-2014, 07:37 AM
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
06-18-2014, 08:00 AM
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
06-18-2014, 08:14 AM
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
06-18-2014, 08:31 AM
Try this array formula

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

wyte32
06-18-2014, 08:51 AM
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
06-18-2014, 10:22 AM
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
06-18-2014, 10:51 AM
omg :dunno duh!! Thanks for pointing that out - works perfect now! Thanks all!