Approximate match on string of text

[Pierre]

I am looking for a solution to the following problem: I have a long list of
companies in an EXCEL sheet, and would like to find, company by company, if
there is an approximate match with a name in another long list.
Concretely, I would like to be able to match “Blue Circle Productions†with
“Blue Circle Productionâ€. Any idea? How would I set the degree of tolerance?

 

[Mike H]

Hi,

What do you mean by 'Find', what are you trying to do?

Mike

 

[Dennis Tucker]

I would assume that when you match “Blue Circle Productions†with “Blue
Circle Productionâ€, you would delete one or the other entry. Is that right?

Are you merging the two lists?

Dennis

 

[Jacob Skaria]

You could try and match the first n characters. Suppose in Col A and Col B
you have the lists. If you need to compare the names in Col B with the ones
in Col A; and return a "YES" or the count of matches...

Comparing the first 10 characters of B1

C1
=COUNTIF(A:A,LEFT(B1,10)& "*")

If this post helps click Yes

 

[Pierre]

Sorry, I was not precise enough. I would like to flag the names in the first
list that are "close enough" to any name in the second list.
It is a kind of VLOOKUP but with a bit of "fuzzy logic" in it.

 

[Jacob Skaria]

You could also try MATCH() function which returns the row number as below.
Again with the first 10 chrs...

=MATCH(LEFT(B1,10) & "*",A:A,0)

If this post helps click Yes

 

[Mike H]

Hi

=VLOOKUP("*Blue*",A1:B20,2,FALSE)

Mike

Sorry, I was not precise enough. I would like to flag the names in the first
list that are "close enough" to any name in the second list.
It is a kind of VLOOKUP but with a bit of "fuzzy logic" in it.

 

[Greg Lovern]

Hi Pierre,

You might want to try itISFUZZYMATCH() and itFUZZYCOMPARE(), the fuzzy
matching worksheet functions in inspector text:

http://precisioncalc.com/it/itISFUZZYMATCH.html
http://precisioncalc.com/it/itFUZZYCOMPARE.html

They both give you very detailed control over the degree of tolerance
of the fuzzy match.


You'll need to install the Free Edition, which never expires:

http://precisioncalc.com/it/index.html


Good luck,

Greg Lovern
mailto:[email protected]
http://PrecisionCalc.com
More Power In Excel