Fuzzy Match Nickname Table for Abbreviations & Acronyms

Hi,

I'm trying to fuzzy match 2 data sets, I have an individuals data each with a list of products that they own. 1 Data set contains a clean name, with the name written out in its entirety with no errors. In the other data set, it is heavily abbreviated. Most are multi word, and the abbreviations and acronyms could mean a few different things.

For example

"Product Item Name Something Else"

Would be something like

"Prod It Nm Som El" or "PIN SE"

I've built a custom 'Nick Names' table, for the common type of abbreviations and acronyms, all is working great, where the mapping is 1 word to 1 word.

"Prod", "Product"

"It", "Item"

"Nm", "Name"

etc

But it doesn't work when there's spaces within the values.

"PIN", "Product Item Number"

Is this just a limitation of the tool that it can't handle Acronyms?

Fuzzy Match

Accepted answers

KayTannee

I'm sure there's going to be better ways of doing this, but this is basically what I've ended up with that does the job. First I filter for values where GroupName Contains a space, used the append to build a unique record for every combination. Then use a formula to swap out where it exists in list, filter to remove any where no match occurred. Then rinse repeat. In this example I'm only assuming there'll be a maximum of 3 abbreviations in any one value, which isn't great. I've seen something called the iterative macro, not sure how to use it yet, but guessing will allow me to loop this logic until there's no results returned.

I use this formula to find/replace, so that it'll only do it if its a 'word' not a couple letters in the middle of another word. Probably should split it out so not Elseifs, incase the same abbreviation features twice in the same string. ie 'PIN foo PIN bar PIN'. But it'll do for my current purposes and as an example.

Each value before formula replace is assigned an ID. Then after the fuzzy match, sort by best match, unique and then I join back to the original value, swapping out the formula replaced value.

IF Contains([Description]," "+[Name]+" ") THEN 
Replace([Description]," "+[name]+" "," "+[GroupName]+" ")
ELSEIF Left([Description], length([Name]) + 1) = [Name]+" " THEN
[groupname] + right([Description], length([Description])-length([name]))
ELSEIF right([Description], length([name]) + 1) = " "+[Name] THEN
Left([Description], length([Description])-length([name])) + [groupname]
ELSE
""
ENDIF

All comments

MarqueeCrew

@KayTannee,

Prior to fuzzy matching, I would suggest some degree of normalization of data values in you data. As a generic tool, the fuzzy matching works as a single-tool. Even then, the fuzzy match tool has many switches and options. In production, I suggest multiple levels of matching and combinations of tools. Exact matches should happen outside of the tool (e.g. JOIN). When you have acronyms, you might want to use a FIND REPLACE tool and attempt an exact match on the standardized form of the term (e.g. PIN).

If you are working with large volumes of data, you can use the following trick of the trade:

Store the entire search string as a MD5 Hash value
Once the string is associated to a result value, create a table of the HASH and the RESULT

If you remember the input value and what the answer becomes, then the next time that you see that same input value, you can short-cut the whole matching process and jump straight to the answer. I know that this won't work for all fuzzy matching exercises, but if you are looking for MarqueeCrew in Michigan over and over again, it will.

Cheers,

Mark

fiorano

Hi,

I have a similar issue, I have a large dataset with itemID as a field with many differing item names which need to be mapped to a correct Master Item.

Is the any way of using fuzzy match to score the likelyhood of a ItemID matching a lookup table ??

An example of the itemID is

Speedy Supplies
SpeedySuppliers
Speedy.
Speedy Supplies 10Pack
Speedy Supplies Pack10

Ideally I would need to map all these to 'Speedy Supplies' - at the moment I'm doing this manually in Excel which is taking an age!

Thanks for any advice,

KayTannee

MarqueeCrew

Hi Thank you,

I'm investigating the Find Replace tool, which would do the job - except it only does a 1:1 replacement. It doesn't seem to handle duplicates at all, I think if I can work out how to do the below then it would fix it. But I can't find any tool that will do it, or work out a combination of tools to do it.