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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|