I have a simple macro that maps one list of company names against another,
such that small differences are ignored.

For example, American Packaging Corp would return a match (using this macro)
with American Packaging Inc

I want to improve its accuracy, and to do this I want to store 200 "common"
words in an array, then test to see if a word is contained in this array.

For example:
myCommon = {"American", "Company", "Corporation", "Ltd", "Inc"...)

blnCommon = True if a word exists in the array, and blnCommon = False if it
isn't in the array. This is the line of code that isn't working for me.

If I can get help with just this line of code, then I can muddle on from
there.

Thanks
Daniel

Sub Tester2()
Dim blnCommon As Boolean
Dim searchTerm As String
Dim myCommon As Variant
searchTerm = "Company"
myCommon = Array("American", "Company", "Corporation", "Ltd", "Inc")

blnCommon = Not (IsError(Application.Match(searchTerm, myCommon, 0)))
MsgBox blnCommon


End Sub


--
Regards,
Tom Ogilvy


"Daniel Bonallack" wrote in
message ...
I have a simple macro that maps one list of company names against another,
such that small differences are ignored.

For example, American Packaging Corp would return a match (using this
macro)
with American Packaging Inc

I want to improve its accuracy, and to do this I want to store 200
"common"
words in an array, then test to see if a word is contained in this array.

For example:
myCommon = {"American", "Company", "Corporation", "Ltd", "Inc"...)

blnCommon = True if a word exists in the array, and blnCommon = False if
it
isn't in the array. This is the line of code that isn't working for me.

If I can get help with just this line of code, then I can muddle on from
there.

Thanks
Daniel

Daniel Bonallack wrote:
I have a simple macro that maps one list of company names against another,
such that small differences are ignored.

For example, American Packaging Corp would return a match (using this macro)
with American Packaging Inc

I want to improve its accuracy, and to do this I want to store 200 "common"
words in an array, then test to see if a word is contained in this array.

For example:
myCommon = {"American", "Company", "Corporation", "Ltd", "Inc"...)

blnCommon = True if a word exists in the array, and blnCommon = False if it
isn't in the array. This is the line of code that isn't working for me.

If I can get help with just this line of code, then I can muddle on from
there.

Thanks
Daniel


What's meant by "if a word exists in the array"? Of course a word exists
in the array--200 words exist in the array. Could you give an
illustration of what's being tested against the array?

Alan Beban

Thanks Tom - this is just what I want (as Alan pointed out, my example was
not very clear, so thanks for returning what I needed).

"Tom Ogilvy" wrote:

Sub Tester2()
Dim blnCommon As Boolean
Dim searchTerm As String
Dim myCommon As Variant
searchTerm = "Company"
myCommon = Array("American", "Company", "Corporation", "Ltd", "Inc")

blnCommon = Not (IsError(Application.Match(searchTerm, myCommon, 0)))
MsgBox blnCommon


End Sub


--
Regards,
Tom Ogilvy


"Daniel Bonallack" wrote in
message ...
I have a simple macro that maps one list of company names against another,
such that small differences are ignored.

For example, American Packaging Corp would return a match (using this
macro)
with American Packaging Inc

I want to improve its accuracy, and to do this I want to store 200
"common"
words in an array, then test to see if a word is contained in this array.

For example:
myCommon = {"American", "Company", "Corporation", "Ltd", "Inc"...)

blnCommon = True if a word exists in the array, and blnCommon = False if
it
isn't in the array. This is the line of code that isn't working for me.

If I can get help with just this line of code, then I can muddle on from
there.

Thanks
Daniel

Daniel Bonallack wrote:
Thanks Tom - this is just what I want (as Alan pointed out, my example was
not very clear, so thanks for returning what I needed).

"Tom Ogilvy" wrote:


Sub Tester2()
Dim blnCommon As Boolean
Dim searchTerm As String
Dim myCommon As Variant
searchTerm = "Company"
myCommon = Array("American", "Company", "Corporation", "Ltd", "Inc")

blnCommon = Not (IsError(Application.Match(searchTerm, myCommon, 0)))
MsgBox blnCommon


End Sub


--
Regards,
Tom Ogilvy

Here is another approach with the same functionality. It requires a
reference to Microsoft Scripting Runtime.

Sub jjj2()
Dim blnCommon As Boolean
Dim searchTerm As String
Dim myCommon As Variant
searchTerm = "Company"
Dim arr, Elem
Dim myCommon As Dictionary
Set myCommon = New Dictionary
arr = Array("American", "Company", "Corporation", "Ltd", "Inc")
For Each Elem In arr
myCommon.Add CStr(Elem), Elem
Next
blnCommon = myCommon.Exists(searchTerm)
MsgBox blnCommon
End Sub

Alan Beban

Thanks Alan. Am I right in saying the difference is that your procedure puts
all the elements of the array into an array variable, then you look in that
to see if the word being tested is present?

Daniel

"Alan Beban" wrote:

Daniel Bonallack wrote:
Thanks Tom - this is just what I want (as Alan pointed out, my example was
not very clear, so thanks for returning what I needed).

"Tom Ogilvy" wrote:


Sub Tester2()
Dim blnCommon As Boolean
Dim searchTerm As String
Dim myCommon As Variant
searchTerm = "Company"
myCommon = Array("American", "Company", "Corporation", "Ltd", "Inc")

blnCommon = Not (IsError(Application.Match(searchTerm, myCommon, 0)))
MsgBox blnCommon


End Sub


--
Regards,
Tom Ogilvy

Here is another approach with the same functionality. It requires a
reference to Microsoft Scripting Runtime.

Sub jjj2()
Dim blnCommon As Boolean
Dim searchTerm As String
Dim myCommon As Variant
searchTerm = "Company"
Dim arr, Elem
Dim myCommon As Dictionary
Set myCommon = New Dictionary
arr = Array("American", "Company", "Corporation", "Ltd", "Inc")
For Each Elem In arr
myCommon.Add CStr(Elem), Elem
Next
blnCommon = myCommon.Exists(searchTerm)
MsgBox blnCommon
End Sub

Alan Beban

Daniel Bonallack wrote:
Thanks Alan. Am I right in saying the difference is that your procedure puts
all the elements of the array into an array variable, then you look in that
to see if the word being tested is present?

Daniel


Almost. It puts all the elements of the array into a Dictionary Object,
and then looks in that to see if the sought word is present.

I'm still confused about how it would be used in practice. What's the
point, e.g., of knowing whether "Corporation" is in the
array/dictionary? I have trouble visualizing how that contributes to
improving the accuracy of the matches. That was why I asked for a simple
illustration of that improved accuracy.

Alan Beban
"Alan Beban" wrote:


Daniel Bonallack wrote:

Thanks Tom - this is just what I want (as Alan pointed out, my example was
not very clear, so thanks for returning what I needed).

"Tom Ogilvy" wrote:



Sub Tester2()
Dim blnCommon As Boolean
Dim searchTerm As String
Dim myCommon As Variant
searchTerm = "Company"
myCommon = Array("American", "Company", "Corporation", "Ltd", "Inc")

blnCommon = Not (IsError(Application.Match(searchTerm, myCommon, 0)))
MsgBox blnCommon


End Sub


--
Regards,
Tom Ogilvy

Here is another approach with the same functionality. It requires a
reference to Microsoft Scripting Runtime.

Sub jjj2()
Dim blnCommon As Boolean
Dim searchTerm As String
Dim myCommon As Variant
searchTerm = "Company"
Dim arr, Elem
Dim myCommon As Dictionary
Set myCommon = New Dictionary
arr = Array("American", "Company", "Corporation", "Ltd", "Inc")
For Each Elem In arr
myCommon.Add CStr(Elem), Elem
Next
blnCommon = myCommon.Exists(searchTerm)
MsgBox blnCommon
End Sub

Alan Beban

I'm not sure yet whether it will help.
I'm trying to map one database of 21,000 company names to another of similar
size. My first mapping macro worked by taking the left x characters, and
decreasing x by 1 until a match was found.
The problem was that Donolley Services International Group (The) would not
get a successful map with The Donolley Services International Group, so I
decided to try mapping on non-common words. I have 200 "common" words to
assign to the array, and I figured I would eliminate these when mapping and
focus only on unique words - in this case "Donolley".
I haven't really moved far on this, but that's the plan. Thanks for your
help, and if you have any suggestions, I'd love to hear them. Oh, and if you
want to see the current version (an xla) just let me know.

Daniel

"Alan Beban" wrote:

Daniel Bonallack wrote:
Thanks Alan. Am I right in saying the difference is that your procedure puts
all the elements of the array into an array variable, then you look in that
to see if the word being tested is present?

Daniel


Almost. It puts all the elements of the array into a Dictionary Object,
and then looks in that to see if the sought word is present.

I'm still confused about how it would be used in practice. What's the
point, e.g., of knowing whether "Corporation" is in the
array/dictionary? I have trouble visualizing how that contributes to
improving the accuracy of the matches. That was why I asked for a simple
illustration of that improved accuracy.

Alan Beban
"Alan Beban" wrote:


Daniel Bonallack wrote:

Thanks Tom - this is just what I want (as Alan pointed out, my example was
not very clear, so thanks for returning what I needed).

"Tom Ogilvy" wrote:



Sub Tester2()
Dim blnCommon As Boolean
Dim searchTerm As String
Dim myCommon As Variant
searchTerm = "Company"
myCommon = Array("American", "Company", "Corporation", "Ltd", "Inc")

blnCommon = Not (IsError(Application.Match(searchTerm, myCommon, 0)))
MsgBox blnCommon


End Sub


--
Regards,
Tom Ogilvy

Here is another approach with the same functionality. It requires a
reference to Microsoft Scripting Runtime.

Sub jjj2()
Dim blnCommon As Boolean
Dim searchTerm As String
Dim myCommon As Variant
searchTerm = "Company"
Dim arr, Elem
Dim myCommon As Dictionary
Set myCommon = New Dictionary
arr = Array("American", "Company", "Corporation", "Ltd", "Inc")
For Each Elem In arr
myCommon.Add CStr(Elem), Elem
Next
blnCommon = myCommon.Exists(searchTerm)
MsgBox blnCommon
End Sub

Alan Beban

Just another way...

Option Explicit
Sub testme01()

Dim myCommon As String 'string--not variant
Dim myWord As String

myCommon = ".American.Company.Corporation.Ltd.Inc."

myWord = "." & "american" & "."

If InStr(1, myCommon, myWord, vbTextCompare) 0 Then
MsgBox "found it"
Else
MsgBox "not found"
End If

End Sub

The leading/trailing dots are important.



Daniel Bonallack wrote:

I have a simple macro that maps one list of company names against another,
such that small differences are ignored.

For example, American Packaging Corp would return a match (using this macro)
with American Packaging Inc

I want to improve its accuracy, and to do this I want to store 200 "common"
words in an array, then test to see if a word is contained in this array.

For example:
myCommon = {"American", "Company", "Corporation", "Ltd", "Inc"...)

blnCommon = True if a word exists in the array, and blnCommon = False if it
isn't in the array. This is the line of code that isn't working for me.

If I can get help with just this line of code, then I can muddle on from
there.

Thanks
Daniel

--

Dave Peterson

Daniel Bonallack wrote:
Thanks Alan. Am I right in saying the difference is that your procedure puts
all the elements of the array into an array variable, then you look in that
to see if the word being tested is present?

Daniel
The assignment to the array variable was just a transient (by habit).
It would have been a little clearer had I just presented

.. . .
Set myCommon = New Dictionary
For Each Elem In Array("American","Company","Corporation","Ltd","In c")
myCommon.Add CStr(Elem), Elem
Next
.. . .

Alan Beban