tag:blogger.com,1999:blog-1800518423097919889.post7335916728247102616..comments2023-11-29T10:14:51.879+00:00Comments on Excellerando: VLookup() with fuzzy-matching to get a 'closest match' resultNigel Heffernanhttp://www.blogger.com/profile/08954578765691578714noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-1800518423097919889.post-41841959267047957652012-12-07T10:22:31.903+00:002012-12-07T10:22:31.903+00:00Thank you very much for posting this - very useful...Thank you very much for posting this - very useful today comparing some lists of wines!gumbihttps://www.blogger.com/profile/01823934703283816727noreply@blogger.comtag:blogger.com,1999:blog-1800518423097919889.post-55408904259158155182011-08-30T21:25:44.165+01:002011-08-30T21:25:44.165+01:00Apologies for the delay in replying - or noticing ...Apologies for the delay in replying - or noticing - these comments. I received no email notification from Bloogger, and only read them when, by accident, I changed the scripting settings in my browser and observed a popup window about comments awaiting 'moderation'... I will test your code as sonn as I get the opportunity.Nigel Heffernanhttps://www.blogger.com/profile/08954578765691578714noreply@blogger.comtag:blogger.com,1999:blog-1800518423097919889.post-14260994084277840712011-02-26T11:19:42.828+00:002011-02-26T11:19:42.828+00:00Nigel - I believe the SumOfCommonStrings() routine...Nigel - I believe the SumOfCommonStrings() routine has an error which is easily corrected.<br /><br />When recursive calls are made, the early tests/exits assign SumOfCommonStrings to the length of s1, when it should be iScore PLUS the length of s1.<br /><br />Proposed solution (see dlmille comments):<br /><br />Sub SumOfCommonStrings(...)<br />...<br />f s1 = s2 Then<br /> SumOfCommonStrings = n + iScore 'dlmille modified - needs to carry the score forward<br /> Exit Function<br />End If<br /><br />If n = 0 Or m = 0 Then<br /> Exit Function<br />End If<br /><br />'s1 should always be the shorter of the two strings:<br />If n > m Then<br /> s3 = s2<br /> s2 = s1<br /> s1 = s3<br /> n = Len(s1)<br /> m = Len(s2)<br />End If<br /><br />n = Len(s1)<br />m = Len(s2)<br /><br />' Special case: s1 is n exact substring of s2<br />If InStr(1, s2, s1, Compare) Then<br /> SumOfCommonStrings = n + iScore 'dlmille modified - needs to carry the score forward<br /> Exit Function<br />End If<br />...<br />End Sub<br /><br />Please advise if you concur.<br /><br />Thanks!<br /><br />DaveUnknownhttps://www.blogger.com/profile/10651896115890031034noreply@blogger.comtag:blogger.com,1999:blog-1800518423097919889.post-89403632518481836712011-02-26T11:15:28.317+00:002011-02-26T11:15:28.317+00:00Nigel - I believe I found an error in the SumOfCom...Nigel - I believe I found an error in the SumOfCommonStrings subroutine. When the subroutine exits early as a result of the early test, SumOfCommonStrings is set to the length of s1 if s1=s2, or if instr(1,s2,s1,Compare)>0, while it SHOULD be setting SumOfCommonStrings = iScore PLUS the length of s1 in these two instances. For some reason, any prior iScore is forgotten in the case of a recursive call!<br /><br />Attached, is my proposed solution (see comments by dlmille in the code):<br /><br />Sub SumOfCommonStrings(...)<br />...<br /><br />If s1 = s2 Then<br /> SumOfCommonStrings = n + iScore 'dlmille modified - needs to carry the score forward<br /> Exit Function<br />End If<br />...<br /><br />' Special case: s1 is n exact substring of s2<br />If InStr(1, s2, s1, Compare) Then<br /> SumOfCommonStrings = n + iScore 'dlmille modified - needs to carry the score forward<br /> Exit Function<br />End If<br /><br />...<br /><br />Please advise if you concur.<br /><br />DaveUnknownhttps://www.blogger.com/profile/10651896115890031034noreply@blogger.com