Data blending/fuzzy logic?

[JackDanIce]

Hi,

Couldn't decide if this room or the General Excel and other programmes room was where to post; apologies if in wrong place.

Looking for general comments/experiences for following:

I have a table of data (~10k rows) which needs to be grouped by client name. Data entry has been entered by multiple persons so sheet has client names like:

ABC Limited
ABC Ltd
ABC Emea Ltd

Which needs to be aggregated into a single row.

I'm trying to determine a formula or set of steps to group names as similar as possible and then unfortunately it will be a manual sense check to find matches.

Initial thoughts

- #1 (helper columns)
Create table of words that can be replaced with nullstring (e.g. Limited, Ltd, Inc etc)
Remove spaces
List data alphabetically
A formula that calculates "how much" of the words match e.g. ABCEmea vs ABC (something like LEN and SUBSTITUTE or something)

Problem with this is ABC Ltd may be an entirely separate client to ABC Limited or ABC itself.

- #2
Use something like Alteryx, Vero Analytics or Tableau which supposedly finds links between data sets but unable to determine how suitable their services might be for this task.

Does anyone have experience of matching text in Excel or the above software providers to suggest best approaches for this?

Thank you in advance!
Jack

 

[steve the fish]

Id be creating a company code which is unambiguous. You could then create a lookup table if you wanted consistent company names.

 

[JackDanIce]

Hi Steve,

That's part of the problem, the data set has names but no ID.

Some have their companies number, (manually, I think lifted from Companies House) so that too could have data input error, hence this post!

The provided client name, for all intents and purposes is the ID, hence problem with this data table...

Thanks,
Jack

 

[steve the fish]

Who is entering this data? Yourselves? Id start entering codes rather than descriptions straight away. Id go so far as saying its a manual job to get this right for past data. There is the fuzzy lookup vba code that is around with a google but you still need to take a look to see if its right. Especially when you say ABC Ltd may be different to ABC Limited. Even a human would assume they were one and the same.

 

[JackDanIce]

It's past data I'm having to fix this for, not new entries. And typical, someone started a spreadsheet to store data, a new person took over and so on; data quality control wasn't even considered!

Figured it would have a large amount of manual work, but anything that could suggest matches would be a start.

As mentioned, there are >10k rows of data that needs grouping, new entries after this, will have specific requirements and is mapped out.

There is bound to be some mistakes with this approach, e.g. linking ABC Ltd with ABC *IF* they're not the same company but the number of errors should (I hope!) be significantly less than the number of successful matches compared leaving the data alone as currently is and the user guesses every time.

Will look for fuzzy matching code, but I think I'll be implementing approach #1 mentioned in original post unless anyone suggests improvements or alternatives.