How to find the most similar Word to a word? (Using Excel or Vba)

makiwara · Apr 18, 2018

INPUT:

List1 BASIS (FIX)

all
allow
dog
dumm
elephant
element

List2

alarm
allowance
dogtag
donut
duck
elemental

OUTPUT

List1 BASIS (FIX) List2

all allowance
allow allowance
dog dogtag
dumm duck
elephant elemental
element elemental

I tried to use the FUZZY LOOKUP, but it doesn't return the right values I expect.

Thank you for your help, have a very nice day!

Niteshnitesh · Apr 18, 2018

Try this one
=vlookup("*"&B1&"*",A1:A10,1,0)

Fazza · Apr 18, 2018

Please explicitly detail the way to identify the right values. (Then it will be possible to work on a formula or code to achieve that.)

Fazza · Apr 18, 2018

This seems to work?

=INDEX(List2,MATCH(input_cell&"z",List2,1))

makiwara · Apr 20, 2018

Dear Fazza and Niteshnitesh!

I tried that, but unfortunately it doesn't work correctly. Could you help me plese?
I inserted two random wordlists to represent the problem exactly, so maybe with word lists its easier to understand what the problem is:
https://imgur.com/k1Opij2
So I need a VBA code or a function which examines a word by letter to letter, and returns the value from list 2 which is the most similar to the original "root word". Thank fo your your help, it means to me a lot! have a very nice day!

//original list 1 and list 2 if needed:
List1:
[TABLE="width: 103"]
<colgroup><col></colgroup><tbody>[TR]
[TD]abandon[/TD]
[/TR]
[TR]
[TD]ability[/TD]
[/TR]
[TR]
[TD]able[/TD]
[/TR]
[TR]
[TD]about[/TD]
[/TR]
[TR]
[TD]about[/TD]
[/TR]
[TR]
[TD]above[/TD]
[/TR]
[TR]
[TD]above[/TD]
[/TR]
[TR]
[TD]abroad[/TD]
[/TR]
[TR]
[TD]absence[/TD]
[/TR]
[TR]
[TD]absolute[/TD]
[/TR]
[TR]
[TD]absolutely[/TD]
[/TR]
[TR]
[TD]absorb[/TD]
[/TR]
[TR]
[TD]abuse[/TD]
[/TR]
[TR]
[TD]academic[/TD]
[/TR]
[TR]
[TD]accept[/TD]
[/TR]
[TR]
[TD]acceptable[/TD]
[/TR]
[TR]
[TD]access[/TD]
[/TR]
[TR]
[TD]accident[/TD]
[/TR]
[TR]
[TD]accommodation[/TD]
[/TR]
[TR]
[TD]accompany[/TD]
[/TR]
[TR]
[TD]according to[/TD]
[/TR]
[TR]
[TD]account[/TD]
[/TR]
[TR]
[TD]account[/TD]
[/TR]
[TR]
[TD]accurate[/TD]
[/TR]
[TR]
[TD]accuse[/TD]
[/TR]
[TR]
[TD]achieve[/TD]
[/TR]
[TR]
[TD]achievement[/TD]
[/TR]
[TR]
[TD]acid[/TD]
[/TR]
[TR]
[TD]acknowledge[/TD]
[/TR]
[TR]
[TD]acquire[/TD]
[/TR]
[TR]
[TD]across[/TD]
[/TR]
[TR]
[TD]act[/TD]
[/TR]
[TR]
[TD]act[/TD]
[/TR]
[TR]
[TD]action[/TD]
[/TR]
[TR]
[TD]active[/TD]
[/TR]
[TR]
[TD]activist[/TD]
[/TR]
[TR]
[TD]activity[/TD]
[/TR]
[TR]
[TD]actor[/TD]
[/TR]
[TR]
[TD]actual[/TD]
[/TR]
[TR]
[TD]actually[/TD]
[/TR]
[TR]
[TD]ad[/TD]
[/TR]
[TR]
[TD]adapt[/TD]
[/TR]
[TR]
[TD]add

List2:
[TABLE="width: 132"]
<colgroup><col></colgroup><tbody>[TR]
[TD]abandonedly[/TD]
[/TR]
[TR]
[TD]abasedly[/TD]
[/TR]
[TR]
[TD]abashedly[/TD]
[/TR]
[TR]
[TD]abatedly[/TD]
[/TR]
[TR]
[TD]abatingly[/TD]
[/TR]
[TR]
[TD]abbreviatedly[/TD]
[/TR]
[TR]
[TD]aberrantly[/TD]
[/TR]
[TR]
[TD]abeyantly[/TD]
[/TR]
[TR]
[TD]abhorrently[/TD]
[/TR]
[TR]
[TD]abidingly[/TD]
[/TR]
[TR]
[TD]abjectly[/TD]
[/TR]
[TR]
[TD]ably[/TD]
[/TR]
[TR]
[TD]abnormally[/TD]
[/TR]
[TR]
[TD]abominably[/TD]
[/TR]
[TR]
[TD]abortedly[/TD]
[/TR]
[TR]
[TD]about[/TD]
[/TR]
[TR]
[TD]abrasively[/TD]
[/TR]
[TR]
[TD]abruptly[/TD]
[/TR]
[TR]
[TD]absently[/TD]
[/TR]
[TR]
[TD]absentmindedly[/TD]
[/TR]
[TR]
[TD]absent-mindedly[/TD]
[/TR]
[TR]
[TD]absolutely[/TD]
[/TR]
[TR]
[TD]absorbantly[/TD]
[/TR]
[TR]
[TD]absorbedly[/TD]
[/TR]
[TR]
[TD]absorbingly[/TD]
[/TR]
[TR]
[TD]abstainedly[/TD]
[/TR]
[TR]
[TD]abstentiously[/TD]
[/TR]
[TR]
[TD]abstinently[/TD]
[/TR]
[TR]
[TD]abstractedly[/TD]
[/TR]
[TR]
[TD]abstractly[/TD]
[/TR]
[TR]
[TD]abstrusely[/TD]
[/TR]
[TR]
[TD]absurdly[/TD]
[/TR]
[TR]
[TD]abundantly[/TD]
[/TR]
[TR]
[TD]abusedly[/TD]
[/TR]
[TR]
[TD]abusively[/TD]
[/TR]
[TR]
[TD]abysmally[/TD]
[/TR]
[TR]
[TD]academically[/TD]
[/TR]
[TR]
[TD]acapella[/TD]
[/TR]
[TR]
[TD]acceptingly[/TD]
[/TR]
[TR]
[TD]accessibly[/TD]
[/TR]
[TR]
[TD]accidentally[/TD]
[/TR]
[TR]
[TD]acclamatorily[/TD]
[/TR]
[TR]
[TD]accommodatingly[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

makiwara · Apr 20, 2018

Fazza, can you help me please? I don't know how to achieve my goal :/

Fazza said:
This seems to work?

=INDEX(List2,MATCH(input_cell&"z",List2,1))

Eric W · Apr 20, 2018

makiwara, I don't see where you ever explicitly described "most similar" as Fazza requested. One way of describing similarity between strings is the Levenshtein distance. See:

https://en.wikipedia.org/wiki/Levenshtein_distance

The distance is roughly defined as how many changes are needed to change one string into the other, adds, deletes, changes. I converted the algorithm on that web page to VBA. Then I wrote another function that compares one word to every word on another list, and returns the word with the smallest Levenshtein distance.

To try it, open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. Paste the following code into the window that opens:

Code:

Public Function LevDist(str1 As String, str2 As String)
Dim d() As Long, m As Long, n As Long, i As Long, j As Long, Cost As Long

    m = Len(str1)
    n = Len(str2)
    ReDim d(0 To m, 0 To n)
    
    For i = 1 To m
        d(i, 0) = i
    Next i
    
    For j = 1 To n
        d(0, j) = j
    Next j
    
    For j = 1 To n
        For i = 1 To m
            Cost = IIf(Mid(str1, i, 1) = Mid(str2, j, 1), 0, 1)
            d(i, j) = WorksheetFunction.Min(d(i - 1, j) + 1, d(i, j - 1) + 1, d(i - 1, j - 1) + Cost)
        Next i
    Next j
    
    LevDist = d(m, n)
    
End Function


Public Function GetClosest(str1 As String, r1 As Range)
Dim d1 As Variant, MinDist As Long, i As Long, j As Long, LD As Long

    d1 = r1.Value
    MinDist = 99999
    GetClosest = ""
    
    For i = 1 To UBound(d1)
        For j = 1 To UBound(d1, 2)
            LD = LevDist(str1, CStr(d1(i, j)))
            If LD < MinDist Then
                MinDist = LD
                GetClosest = d1(i, j)
            End If
        Next j
    Next i
        
End Function

Press Alt-Q to close the editor. Then enter the function like this on your worksheet:

	A	B	C	D	E	F
List1	List2
abandonedly	abandon	all	alarm	alarm	all
ably	abandon	allow	alarm	allowance	allow
ably	abandon		dog	dogtag	dogtag	dog
about	abandon	dumm	duck	donut	dog
about	abandon	elephant	elemental	duck	dumm
about	absolutely	element	elemental	elemental	element
about	abandon
about	abandon
absently	absolutely
absolutely	ability>
absolutely	ability
about	able
ably	absolutely
academically	ability
about	absolutely
acceptingly	about
accessibly	absolutely
accidentally	ability
accommodatingly	absence
abnormally	absence
absorbingly	absence
about	absolutely
about	absolutely
about	absolutely
about	absolutely
acapella	absolutely
abhorrently	absolutely
ably	ability
abandonedly	absolutely
about	absolutely
about	absolutely
ably	absolutely
ably	abandon
ably	abuse
ably	abuse
abatingly	actually
abatingly	academic
39	ably	academic
about	acceptable
abruptly	acceptable
ably	accident
about	acceptable
ably	accommodation

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]List1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]List2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]a
bandon[/TD]

[TD="bgcolor: #FAFAFA"]abandonedly[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]ability[/TD]

[TD="bgcolor: #FAFAFA"]abasedly[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]able[/TD]

[TD="bgcolor: #FAFAFA"]abashedly[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]about[/TD]

[TD="bgcolor: #FAFAFA"]abatedly[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]about[/TD]

[TD="bgcolor: #FAFAFA"]abatingly[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]above[/TD]

[TD="bgcolor: #FAFAFA"]abbreviatedly[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]above[/TD]

[TD="bgcolor: #FAFAFA"]aberrantly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]ab
road[/TD]

[TD="bgcolor: #FAFAFA"]abeyantly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]absence[/TD]

[TD="bgcolor: #FAFAFA"]abhorrently[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]absolute[/TD]

[TD="bgcolor: #FAFAFA"]abidingly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA"]absolutely[/TD]

[TD="bgcolor: #FAFAFA"]abjectly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA"]absorb[/TD]

[TD="bgcolor: #FAFAFA"]ably[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA"]abuse[/TD]

[TD="bgcolor: #FAFAFA"]abnormally[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA"]academic[/TD]

[TD="bgcolor: #FAFAFA"]abominably[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA"]accept[/TD]

[TD="bgcolor: #FAFAFA"]abortedly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA"]acceptable[/TD]

[TD="bgcolor: #FAFAFA"]about[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA"]access[/TD]

[TD="bgcolor: #FAFAFA"]abra
sively[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA"]accident[/TD]

[TD="bgcolor: #FAFAFA"]abruptly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FAFAFA"]accommodation[/TD]

[TD="bgcolor: #FAFAFA"]absently[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FAFAFA"]accompany[/TD]

[TD="bgcolor: #FAFAFA"]absentmindedly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FAFAFA"]according to[/TD]

[TD="bgcolor: #FAFAFA"]absent-mindedly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FAFAFA"]account[/TD]

[TD="bgcolor: #FAFAFA"]absolutely[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FAFAFA"]account[/TD]

[TD="bgcolor: #FAFAFA"]absorbantly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FAFAFA"]accurate[/TD]

[TD="bgcolor: #FAFAFA"]absorbedly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #FAFAFA"]accuse[/TD]

[TD="bgcolor: #FAFAFA"]absorbingly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #FAFAFA"]achieve[/TD]

[TD="bgcolor: #FAFAFA"]abstainedly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: #FAFAFA"]achievement[/TD]

[TD="bgcolor: #FAFAFA"]abstentiously[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="bgcolor: #FAFAFA"]acid[/TD]

[TD="bgcolor: #FAFAFA"]abstinently[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="bgcolor: #FAFAFA"]acknowledge[/TD]

[TD="bgcolor: #FAFAFA"]abstractedly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="bgcolor: #FAFAFA"]acquire[/TD]

[TD="bgcolor: #FAFAFA"]abstractly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32d>[/TD]
[TD="bgcolor: #FAFAFA"]across[/TD]

[TD="bgcolor: #FAFAFA"]abstrusely[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="bgcolor: #FAFAFA"]act[/TD]

[TD="bgcolor: #FAFAFA"]absurdly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="bgcolor: #FAFAFA"]act[/TD]

[TD="bgcolor: #FAFAFA"]abundantly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="bgcolor: #FAFAFA"]action[/TD]

[TD="bgcolor: #FAFAFA"]abusedly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="bgcolor: #FAFAFA"]active[/TD]

[TD="bgcolor: #FAFAFA"]abusively[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]<
/td>[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="bgcolor: #FAFAFA"]activist[/TD]

[TD="bgcolor: #FAFAFA"]abysmally[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="bgcolor: #FAFAFA"]activity[/TD]

[TD="bgcolor: #FAFAFA"]academically[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FAFAFA"]actor[/TD]

[TD="bgcolor: #FAFAFA"]acapella[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="bgcolor: #FAFAFA"]actual[/TD]

[TD="bgcolor: #FAFAFA"]acceptingly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]
[TD="bgcolor: #FAFAFA"]actually[/TD]

[TD="bgcolor: #FAFAFA"]accessibly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]42[/TD]
[TD="bgcolor: #FAFAFA"]ad[/TD]

[TD="bgcolor: #FAFAFA"]accidentally[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]43[/TD]
[TD="bgcolor: #FAFAFA"]adapt[/TD]

[TD="bgcolor: #FAFAFA"]acclamatorily[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]44[/TD]
[TD="bgcolor: #FAFAFA"]add[/TD]

[TD="bgcolor: #FAFAFA"]accommodatingly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet
Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=getclosest(A2,$C$2:$C$44)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Note that this does NOT return all the results you expect. For example, on your sheet you show "ability" returning "abasedly", while on my sheet I get "ably". The Levenshtein distance between "ability" and "abasedly" is 5 (change "ilit" to "ased", 4 changes, and insert "l"). The Levenshtein distance between "ability" and "ably" is 3 (delete "i" and "it").

So there you go. If this does not return the results you want, you'll need to describe how to get those results.

makiwara · Apr 21, 2018

You are awesome!!!! Thank you a lot works perfectly!!!

Eric W said:
makiwara, I don't see where you ever explicitly described "most similar" as Fazza requested. One way of describing similarity between strings is the Levenshtein distance. See:

https://en.wikipedia.org/wiki/Levenshtein_distance

The distance is roughly defined as how many changes are needed to change one string into the other, adds, deletes, changes. I converted the algorithm on that web page to VBA. Then I wrote another function that compares one word to every word on another list, and returns the word with the smallest Levenshtein distance.

To try it, open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. Paste the following code into the window that opens:

Code:

Public Function LevDist(str1 As String, str2 As String) Dim d() As Long, m As Long, n As Long, i As Long, j As Long, Cost As Long m = Len(str1) n = Len(str2) ReDim d(0 To m, 0 To n) For i = 1 To m d(i, 0) = i Next i For j = 1 To n d(0, j) = j Next j For j = 1 To n For i = 1 To m Cost = IIf(Mid(str1, i, 1) = Mid(str2, j, 1), 0, 1) d(i, j) = WorksheetFunction.Min(d(i - 1, j) + 1, d(i, j - 1) + 1, d(i - 1, j - 1) + Cost) Next i Next j LevDist = d(m, n) End Function Public Function GetClosest(str1 As String, r1 As Range) Dim d1 As Variant, MinDist As Long, i As Long, j As Long, LD As Long d1 = r1.Value MinDist = 99999 GetClosest = "" For i = 1 To UBound(d1) For j = 1 To UBound(d1, 2) LD = LevDist(str1, CStr(d1(i, j))) If LD < MinDist Then MinDist = LD GetClosest = d1(i, j) End If Next j Next i End Function

Press Alt-Q to close the editor. Then enter the function like this on your worksheet:

A B C D E F G H I
List1 List2
abandonedly abandon all alarm alarm all
ably abandon allow alarm allowance allow
ably abandon dog dogtag dogtag dog
about abandon dumm duck donut dog
about abandon elephant elemental duck dumm
about absolutely element elemental elemental element
about abandon
about abandon
absently absolutely
absolutely ability>
absolutely ability
about able
ably absolutely
academically ability
about absolutely
acceptingly about
accessibly absolutely
accidentally ability
accommodatingly absence
abnormally absence
absorbingly absence
about absolutely
about absolutely
about absolutely
about absolutely
acapella absolutely
abhorrently absolutely
ably ability
abandonedly absolutely
about absolutely
about absolutely
ably absolutely
ably abandon
ably abuse
ably abuse
abatingly actually
abatingly academic
39 ably academic
about acceptable
abruptly acceptable
ably accident
about acceptable
ably accommodation

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]List1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]List2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]a
bandon[/TD]

[TD="bgcolor: #FAFAFA"]abandonedly[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]ability[/TD]

[TD="bgcolor: #FAFAFA"]abasedly[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]able[/TD]

[TD="bgcolor: #FAFAFA"]abashedly[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]about[/TD]

[TD="bgcolor: #FAFAFA"]abatedly[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]about[/TD]

[TD="bgcolor: #FAFAFA"]abatingly[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]above[/TD]

[TD="bgcolor: #FAFAFA"]abbreviatedly[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]above[/TD]

[TD="bgcolor: #FAFAFA"]aberrantly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]ab
road[/TD]

[TD="bgcolor: #FAFAFA"]abeyantly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]absence[/TD]

[TD="bgcolor: #FAFAFA"]abhorrently[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]absolute[/TD]

[TD="bgcolor: #FAFAFA"]abidingly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA"]absolutely[/TD]

[TD="bgcolor: #FAFAFA"]abjectly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA"]absorb[/TD]

[TD="bgcolor: #FAFAFA"]ably[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA"]abuse[/TD]

[TD="bgcolor: #FAFAFA"]abnormally[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA"]academic[/TD]

[TD="bgcolor: #FAFAFA"]abominably[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA"]accept[/TD]

[TD="bgcolor: #FAFAFA"]abortedly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA"]acceptable[/TD]

[TD="bgcolor: #FAFAFA"]about[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA"]access[/TD]

[TD="bgcolor: #FAFAFA"]abra
sively[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA"]accident[/TD]

[TD="bgcolor: #FAFAFA"]abruptly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FAFAFA"]accommodation[/TD]

[TD="bgcolor: #FAFAFA"]absently[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FAFAFA"]accompany[/TD]

[TD="bgcolor: #FAFAFA"]absentmindedly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FAFAFA"]according to[/TD]

[TD="bgcolor: #FAFAFA"]absent-mindedly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FAFAFA"]account[/TD]

[TD="bgcolor: #FAFAFA"]absolutely[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FAFAFA"]account[/TD]

[TD="bgcolor: #FAFAFA"]absorbantly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FAFAFA"]accurate[/TD]

[TD="bgcolor: #FAFAFA"]absorbedly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #FAFAFA"]accuse[/TD]

[TD="bgcolor: #FAFAFA"]absorbingly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #FAFAFA"]achieve[/TD]

[TD="bgcolor: #FAFAFA"]abstainedly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: #FAFAFA"]achievement[/TD]

[TD="bgcolor: #FAFAFA"]abstentiously[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="bgcolor: #FAFAFA"]acid[/TD]

[TD="bgcolor: #FAFAFA"]abstinently[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="bgcolor: #FAFAFA"]acknowledge[/TD]

[TD="bgcolor: #FAFAFA"]abstractedly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="bgcolor: #FAFAFA"]acquire[/TD]

[TD="bgcolor: #FAFAFA"]abstractly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32d>[/TD]
[TD="bgcolor: #FAFAFA"]across[/TD]

[TD="bgcolor: #FAFAFA"]abstrusely[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="bgcolor: #FAFAFA"]act[/TD]

[TD="bgcolor: #FAFAFA"]absurdly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="bgcolor: #FAFAFA"]act[/TD]

[TD="bgcolor: #FAFAFA"]abundantly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="bgcolor: #FAFAFA"]action[/TD]

[TD="bgcolor: #FAFAFA"]abusedly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="bgcolor: #FAFAFA"]active[/TD]

[TD="bgcolor: #FAFAFA"]abusively[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]<
/td>[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="bgcolor: #FAFAFA"]activist[/TD]

[TD="bgcolor: #FAFAFA"]abysmally[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="bgcolor: #FAFAFA"]activity[/TD]

[TD="bgcolor: #FAFAFA"]academically[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FAFAFA"]actor[/TD]

[TD="bgcolor: #FAFAFA"]acapella[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="bgcolor: #FAFAFA"]actual[/TD]

[TD="bgcolor: #FAFAFA"]acceptingly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]
[TD="bgcolor: #FAFAFA"]actually[/TD]

[TD="bgcolor: #FAFAFA"]accessibly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]42[/TD]
[TD="bgcolor: #FAFAFA"]ad[/TD]

[TD="bgcolor: #FAFAFA"]accidentally[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]43[/TD]
[TD="bgcolor: #FAFAFA"]adapt[/TD]

[TD="bgcolor: #FAFAFA"]acclamatorily[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]44[/TD]
[TD="bgcolor: #FAFAFA"]add[/TD]

[TD="bgcolor: #FAFAFA"]accommodatingly[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet
Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]=getclosest(A2,$C$2:$C$44)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Note that this does NOT return all the results you expect. For example, on your sheet you show "ability" returning "abasedly", while on my sheet I get "ably". The Levenshtein distance between "ability" and "abasedly" is 5 (change "ilit" to "ased", 4 changes, and insert "l"). The Levenshtein distance between "ability" and "ably" is 3 (delete "i" and "it").

So there you go. If this does not return the results you want, you'll need to describe how to get those results.

Eric W · Apr 21, 2018

You're welcome!

makiwara · Apr 22, 2018

Dear Eric! I tested today the code

Eric W said:
You're welcome!

How to find the most similar Word to a word? (Using Excel or Vba)

makiwara

Board Regular

Excel Facts

Niteshnitesh

New Member

Fazza

MrExcel MVP

Fazza

MrExcel MVP

makiwara

Board Regular

makiwara

Board Regular

Eric W

MrExcel MVP

makiwara

Board Regular

Eric W

MrExcel MVP

makiwara

Board Regular

Similar threads

Forum statistics

Share this page

How to find the most similar Word to a word? (Using Excel or Vba)

Board Regular

Excel Facts

New Member

MrExcel MVP

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock