To return a list of near matches from a long list of strings would be quite difficult in VBA (unless I’m missing something), but in Python it is very simple. Here is the code for this operation, using pyxll to read a list from Excel, and return the near matches as the result of a User Defined Function (UDF):
from difflib import get_close_matches
@xl_func
@xl_arg('patterns', 'str[]')
@xl_arg('n', 'int')
def closeMatches(patterns, word, n =3, cutoff = 0.6):
return get_close_matches(word, patterns, n, cutoff)
The function required arguments are:
- patterns: A single column list of text strings
- word: A single word to find close matches for in the list.
By default the function returns a maximum of 3 results.
The example below looks for matches to “py_GetBeamSection” in the list of 1800+ function names in column R, returning 3 matches:
The first optional argument, ‘n’, specifies the maximum number of strings to return, set to 30 below:
This list can be reduced with the second optional argument, ‘cutoff’, which has a default value of 0.6. Increasing this to 0.75 reduces the number of matches to 11 in this case:
Note that for the particular application of finding function names the Excel function wizard also does a pretty good job. Entering ‘=py_GetBeamSe’ returns a list of the 7 closest available functions (all UDFs written in python in this case):
Select the function you want from the list and press tab, and the function will be entered on the edit line. Then click the ‘Insert Function” icon (top left in the screen-shot below), and the function is listed together with a list of arguments, and where available, the function and argument descriptions included in the code:
Newton Excel Bach, not (just) an Excel Blog 