percentual match of cells

[danovkos]

Hi all,
exist any function (not with vba code) that will compare 2 cells and return if there are 80% match "Its OK" else return "NOTOK"
I try to compare 2 columns of names and numbers.
Or beeter, it will return percent number of match and result "OK" or "NOT OK".
I supposed, that will seems, something like this:

John Doe▲Jhn Davi▲70% NOT OK
john doe▲ john doe ▲100% OK
peter keneyon78 ▲peter karry20 ▲65% NOT OK

in this my example the percentual numbers are only my guess.

thank you for your help

[danovkos]

or is it possible only with VBA?
if yes, how can do it?
thx

[Aussiebear]

You will need to define a set of rules so the code can match against the desired percentage of match. Can you define these first and then the forum can try to assist you.

[danovkos]

Definition of rules:
compare cel A1 and B1 and result give in C1
A1 = john doe B1= john BLACK

limit for ok is 80%

1) How many from character in B1 is in A1
2) if all (9) result is „100% = OK“, if 4 result is 44%= NOT OK“, if 1 result is 11%= NOT OK“

And i hope it will be NOT case sensitive.

Do you think, is it enough for definition of my rules or should i something more specify?

[mikerickson]

So "Arthur T. Smith" is a 100% match of "Arthur A. Smith"?

What is the match % between "abc" and "ab"
is that the same as the match % between "ab" and "abc"

[danovkos]

abc and ab is not the same as ab and abc
because if i search abc(B1) in ab(A1) it will find only 2 from 3 character and it will be only 66%
do you know what i mean?
and the arthur example is 100%

[mikerickson]

I've got some ideas on how to do this
One more question "abc" vs "abd", is this 66% (2 chr match out of 3)

Is match%("abc","abd") = match%("abc","ab")?

(hmm.. triangle inequality?? Match(a,c) <= match(a,b)*match(b,c) for all a,b,c)

[mikerickson]

The UDF MatchPct will return the kind of value that you want


Public Function matchPct(aStr As String, bStr As String) As Double
If 0 < Len(aStr) * Len(bStr) Then
matchPct = SameLetterPct(aStr, bStr) * SameLetterPct(bStr, aStr)
End If
End Function

Private Function SameLetterPct(aStr As String, bStr As String) As Double
Dim i As Long
If 0 < Len(aStr) * Len(bStr) Then
For i = 1 To Len(aStr)
SameLetterPct = SameLetterPct + Sgn(InStr(1, bStr, Mid(aStr, i, 1), 1))
Next I
SameLetterPct = SameLetterPct / Len(aStr)
End If
End Function

If you want native Excel, this formula will return the same value, unless one of the strings is "", in which case, this returns an error (the UDF returns 0)


=SUMPRODUCT(--ISNUMBER(SEARCH(MID(A1&REPT(CHAR(5),255),ROW(INDIRECT("1:255")),1),B1))) / LEN(A1) _
* SUMPRODUCT(--ISNUMBER(SEARCH(MID(B1&REPT(CHAR(5),255),ROW(INDIRECT("1:255")),1),A1))) / LEN(B1)

[danovkos]

yes, it works great :)
thank you very match for your help