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.

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:

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.

xld
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!