Partial matches

[plant007]

Hi

I am really struggling with matching text strings across two datasets which use different terminology. I have used a vlookup (within an IF and ISNA), with wildcards to match certain text strings but this only works when the original text string is part of the second text string ie SoS is part of ABC SOS DEF, but most of the matches I need to make are as follows;

GHI SOS JKL as part of ABC SOS DEF

which means I need to match 3-4 contiguous letters within both text strings, without using macros

Any help would be most appreciated

Thanks
Any

 

[sheetspread]

As Alan said:

Algorithm
Defines the algorithm to be used for matching strings. Valid values are 1, 2 or 3:
Algorithm = 1
This algorithm is best suited for matching mis-spellings.
For each character in 'String1', a search is performed on 'String2'.
The search is deemed successful if a character is found in 'String2' within 3 characters of the current position.
A score is kept of matching characters which is returned as a percentage of the total possible score.
Algorithm = 2
This algorithm is best suited for matching sentences, or 'firstname lastname' compared with 'lastname firstname' combinations.
A count of matching pairs, triplets, quadruplets etc. in 'String1' and 'String2' is returned as a percentage of the total possible.
Algorithm = 3: Both Algorithms 1 and 2 are performed.
Default: 3

NFpercent
The Percentage value below which matching strings are deemed as not found. If no strings in the lookup table equal or exceed this matching percentage, #N/A is returned.
The higher the percentage specified, the higher the confidence level in the returned result.
Default: 5%

Rank
An optional parameter which may take any value > 0 and causes the function to return the specified ranking best match.
Default: 1

Algorithm 2 sounds like what you want, but it's hard to be sure without seeing your data.

can't understand why higher ranked results have no reference to FBC / Approval etc at all.

There's the misspelling vs word order problem....

 

[plant007]

I read these and it may be best to give an example, after changing to algorithm 2 and reducing NFpercent ot 0.01, using rank 1;

I searched for 'Approval - HMT OBC' and instead of picking up 'Outline Business Case (OBC) for programme', it picks up 'Appraisal of Sustainability (AoS) - Assurance Report of ACs Sustainability Appraisal Report'...I assume it is picking up the APPR (appears twice) section rather than the OBC section?

There is unfortunately like this example, no regularity

 

[sheetspread]

Ideally you'd have a three letter acronym in parentheses in each or a list you could match to. Otherwise you are going to need a lot of visual inspection along with the formula (which is hopefully much better than nothing).

 

[plant007]

tried the list with three acronyms / parentheses and picking up lots of wrong answers - have a feeling its going to be a manual process

 

[sheetspread]

If you were to compile a list of keywords, how long would it be?