Compare Last name in Column to Business Name in another column

A

ADArnold

Hi All,

I have 2 columns:

Column A is "Last Name"
Column B is "Business Name"

I would like to indicate in Column C if the last name is locate
anywhere in the Business Name column. For example:


Column A = "Smith"
Column B = "Smith, Varney, Ross Insurance Inc."

or

Column A = "Smith"
Column B = "John J. Smith, M.D. LLC"

Either of the above would give a match and need to be indicated in
third column with text like "Matched" or something similar.

I'm sure this is a simple text string comparison, but you 'guys' woul
save me a lot of messing around to figure it out. Thanks for you
assistance (Again).

A
 
F

Frank Kabel

Hi
try something like
=IF(COUNTIF($B$1:$B$100,"*" & A1 & "*"),"Match","no match")
 
D

Dave Peterson

You could use a formula like:

=if(countif(b:b,"*" & a1 & "*")>0,"Matched","NotMatched")
 
A

ADArnold

Many thanks to both Dave and Frank!
I tried both solutions and they do what I want.

but...

If Column A has "Smith" and Column B has "Smithy" or the somethin
similar like "Jones" and "Jonesy", then Column C indicates a match.

If I have Smith in A and Smith-Jones in B, then no match. Hmmm.

Well, please don't think I don't really appreciate your extremely quic
solutions!

Just where would I be without your help!!!??? Nowhere!

Thanks again and have a great weekend!
A
 
B

Bob Phillips

Not in my tests they don't. Are you sure that A doesn't contain a space or
something?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
A

ADArnold

Hi Bob,

I went back and checked the sample SS and I re-entered the text t
check.
Nope, I had smith in Col A and Smithy in Col B and got a match. If
attached the sample XLS correctly, you can check it. I can't find m
error
 
D

Dave Peterson

That kind of confirms what Bob suggested--extra characters in the cell.

Chip Pearson has an addin that can help you find out what is exactly in that
cell.
http://www.cpearson.com/excel/CellView.htm

If they're simply leading/trailing spaces, you could change the formula to:

=if(countif(b:b,"*" & trim(a1) & "*")>0,"Matched","NotMatched")

But I've always found it better to actually fix those offending cells. (It's
easier to clean it up once than to remember to remember to adjust every formula
that may refer to those cells.
 
A

ADArnold

Hi Dave,
I downloaded and installed Chip's file and find no extra characters i
any of the cells in column A when executing 'cell view'.

I have attached the zip file of the sample spreadsheet. I have adde
each of the offered solutions in separate columns with the results o
the operation.

If you get a moment to check it out, you may find the problem
Personally, I have a feeling that the "*" trailing A1 is the problem
as it sees Jones and Jonesy, or Jonesysysy all as the same because i
appears to take the name and no matter what seems to follow (except
space) as a match.

Thanks for your help!
A

+-------------------------------------------------------------------
|Filename: test.zip
|Download: http://www.excelforum.com/attachment.php?postid=2798
+-------------------------------------------------------------------
 
D

Dave Peterson

First, since I connect to the MS newsservers directly, I don't see your
attachment. Second, even if I could see the attachment, I wouldn't open it--too
much chance of malicious things happening.

And I was just addressing why Jones wasn't found in column B. (kind of a false
negative).

The problem with Jones matching Jonesysysy is essentially a false positive. It
really matches (the words Jones is found in a cell that contains Jonesysysy).

This problem is more difficult to resolve. If you could besure that column B
had Jones as a word (surrounded by spaces), then Frank's suggestion would be the
way to go.

But if you had Jones-Smyth in column B, it might cause trouble--depending on if
you considered that a match.
 
B

Bob Phillips

I have checked the file and as far as I can see, you're description of what
you wanted and what you were getting bears little relation to what is
actually there.

I fail to see how Frank's original formula 'works' when the desired results
to Frank's results give

Match Match
Match Match
Match Match
No Match Match
No Match Match
No Match Match
No Match No Match
Match Match
No match Match
Match Match
No Match Match


I thbink what you are looking for is closer to
=IF(ISNUMBER(SEARCH(A13,B13)),"Match","No Match ")
which gives
Match Match
Match Match
Match Match
No Match No Match
No Match No Match
No Match No Match
No Match Match
Match Match
No match Match
Match Match
No Match Match

but this will still return a match for Smith in Smithy.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
A

ADArnold

Thanks Dave.
I agree with you on the attachments and the solutions that were s
helpfully given by you and others will do just fine. I appreciate th
time you have spent on this.
Hope your weather is as nice as ours here on the SW Gulf Coast o
Florida! Should get to 80+ today. Hmmm... after all this slaving over
hot keyboard, I think I'll hit the pool in a while! <LOL)!
Take Care!
A
 
A

ADArnold

Bob,
Thanks for the input. I probably screwed up the attached file afte
messing with all the solutions, so I'll need to start over. All th
solutions given (except 1) worked for the most part. Thanks again fo
your input. It's appreciated.
A
 
A

ADArnold

Hi Bob,
Here's the file again after I re-entered the solutions. It appears your
solution worked the best with the exceptions that you mentioned. I
noticed that if the Name was longer than the similar text in the
business column, there was a 'Not Matched". For example Col A: Jones
Col B: Jonesy equaled 'no match', but when reversed, it was a 'match'.
Thanks,
AA


+-------------------------------------------------------------------+
|Filename: Compare text in 2 Columns (B).zip |
|Download: http://www.excelforum.com/attachment.php?postid=2799 |
+-------------------------------------------------------------------+
 
Top