Matching two lists with multiple words per line

[Mike Wilson]

I've been on the journey of figuring out how to achieve a matching between two lists. There is a master list of formal company names. There is a list of store names that contains the city and the a partial company name. I need to get to the parts of each line that match. My first go around on this is very very clunky. By experience, the great minds in Alphaland have knowledge of just those best functions and just how to align them to achieve a best outcome. Anyone game at giving this an effort? A little like re-kindling Tom Cone's puzzles...

dim vLretailers as C
vLretailers=<<%s%
Shaw's & Star Markets
Food Circus Supermarkets
Waverly Cash Saver - AWG
Goode's Cash Saver - AWG
Great Giant Supermarket
Giant Eagle
Giant Food Stores
%s%

dim vLchallenge as C
vLchallenge=<<%c%
Berlin Corners Shaw's 616
Marlin Food Circus 2133
Watertown Giant Eagle 1841
Crestville Giant Food 57
Garland Less Cash Mart 121
%c%

results:
Shaw's
Food Circus
Giant Eagle
Giant Food
<no match>

[Mike Wilson]

After many hours of working on this, I recognize a I cannot achieve a fully automated code solution. There is no way to have logic discern a challenge with Great Falls Safeway and it not matching with Great Giant Supermarkets and also work to have Williston Shaw's match to Saw's & Star Markets. The need to have a single word matching for one challenge returns an incorrect match with other challenges. I guess I'll have to have a user selection dialog, but this is an unfavorable option when the conversion involves 4000 records.

[Ted Giles]

Mike, I fiddled for a while with this problem.
Came to a similar conclusion.
However, I didn't finish the exploration and was going into setting "sounds like" and adding a complicated Soundex value for each word.
Is there no way you can create a Parent/Children company tabe? Or is that what you are trying to do?

[Mike Wilson]

Hi Ted,
You are a champion! I was starting with if this was simple, have the eval happen on table export and export data rectified. No joy there. So I resorted to an extra field in the table that holds the exact values I need even if the value has the data present in other fields. What is the saying - normilize only to the point things don't work. New field as a compilation of other fields - works for me.

[Davidowicza]

Another way of looking at it is if you had a master list of all towns that are in the area you are working with. Then you can loop through the town names to see if one exists in the name, if so, strtran() it out of the challenge name. Then you can remove the last word in the challenge name since it seems like they are always numerical. What's left is a portion of the store name you are looking for, where you can then use the $ operator to match it up with a formal name. It may seem like a lot, but I feel it would work.

If I had time I would write this out in code, but unfortunately do not today.

[Mike Wilson]

Hi David,
Interesting thought. However, the key there is "if you had a master list of all towns". Yeay, two problems are that while 90% of the time it is a town name that prefixes, sometimes it is a county name or sometimes even a mall name, and not always town names. Also, the current Project I'm working on is with stores in PA, NY, NJ, RI, MA, VT, NH. Not thinking a master town list is available.