Matching two strings and transferring data

A quick overview:

I am creating two data tables from sets of company data - Invoice Data and FAME Data

For the Invoice Data i am updating it so that for any records that contain Ltd or Limited i am removing the end of the text. e.g. ‘Kenstone Properties Limited’ becomes just ‘Kenstone Properties’.

I am then trying to match the Invoice Data with the FAME Data. The FAME data may contain e.g. ‘Kenstone Properties Limited’ so i want to see if the Invoice data “Contains” the same.

Issues i am encountering:
(i) I am unable to return a positive match. Though there should be at least one match between ‘Kenstone Properties Limited’ in the Invoice Data to ‘Kenstone Properties Ltd’ in the FAME Data.

(ii) If a match is found i want to enter into the Invoice Data Table a “Yes” in the ‘Match’ column i have added. If there is no match then a “No” is entered.

(iii) If a match is found as “Yes” then it should extract from the FAME Data and enter it into the Invoice Data. I have setup the variables for the FAME Data e.g. stFAMERegisteredNumber etc

FAME Validation.xaml (31.4 KB)

Invoice Data.xlsx (678.2 KB)

CosOver5Employees_UKEir.xlsx (30.0 KB)

As an alternative, you could consider fuzzy string matching. One possible algorithm is calculating the Levenshtein distance, and here’s an implementation in VB.NET. You could still filter specific stop-words such as Limited/Ltd, Inc et cetera before calculating the distance.