A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Approximate match on string of text



 
 
Thread Tools Display Modes
  #1  
Old May 4th, 2009, 01:46 PM posted to microsoft.public.excel.worksheet.functions
Pierre
external usenet poster
 
Posts: 223
Default Approximate match on string of text

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?

  #2  
Old May 4th, 2009, 02:13 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 8,419
Default Approximate match on string of text

Hi,

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

Mike


"Pierre" wrote:

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?

  #3  
Old May 4th, 2009, 02:16 PM posted to microsoft.public.excel.worksheet.functions
Dennis Tucker
external usenet poster
 
Posts: 67
Default Approximate match on string of text

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

"Pierre" wrote in message
...
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?

  #4  
Old May 4th, 2009, 02:17 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Approximate match on string of text

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
---------------
Jacob Skaria


"Pierre" wrote:

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?

  #5  
Old May 4th, 2009, 02:42 PM posted to microsoft.public.excel.worksheet.functions
Pierre
external usenet poster
 
Posts: 223
Default Approximate match on string of text

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.

"Dennis Tucker" wrote:

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

"Pierre" wrote in message
...
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?


  #6  
Old May 4th, 2009, 02:55 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Approximate match on string of text

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
---------------
Jacob Skaria


"Pierre" wrote:

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.

"Dennis Tucker" wrote:

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

"Pierre" wrote in message
...
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?


  #7  
Old May 4th, 2009, 03:00 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 8,419
Default Approximate match on string of text

Hi

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

Mike

"Pierre" wrote:

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.

"Dennis Tucker" wrote:

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

"Pierre" wrote in message
...
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?


  #8  
Old May 16th, 2009, 10:27 PM posted to microsoft.public.excel.worksheet.functions
Greg Lovern
external usenet poster
 
Posts: 54
Default Approximate match on string of text

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

http://PrecisionCalc.com
More Power In Excel



On May 4, 5:46*am, Pierre wrote:
I am looking for a solution to the following problem: I have a long list of
companies in an EXCEL sheet, and would like tofind, 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?


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:28 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.