match letters in one field to letters in another and display % match

[lala]

Hi, wonder if this is even doable

i want to match 2 fields, but there's no 100% matches in any of the entries. so what i need done is somehow to be able to compare all the letters (however many times they appear) to letters in the second field and then based on how many letters matched display a % matched

for example, if the 2 fields are

nata natalie

total number of letters is 11, 8 matched
so the % will be 8/11=72%

i couldve messed up, math is not my strength. but you get the idea? maybe there's a better way to calculate the %, i'd appreciate if anyone has suggestions

thank you

 

[vbaInet]

You would need to create a function for this. The function to use are Mid(). Not quite an easy task if you don't know VBA.

1. Count the length of both strings
2. Create a loop to iterate using the length of the shortest string
3. Compare each character of both strings.
4. Perform the percentage calculation.

Sounds like a coursework to me?

 

[lala]

i know vba a little, but how would i use mid? i already found a function that counts the number of occurences of each letter, now i'm trying to think of a best way to compare them.
but use Mid for what? to go through each letter?
and the harder the more interesting)))))))))) to me at least

 

[lala]

Public Function fCountOccur(strSource As String, strMatch As String) As String
Dim iCount As Integer
Dim iPosition As Integer
    iCount = 0
    For iPosition = 1 To Len(strSource)
        If Mid(strSource, iPosition, 1) = strMatch Then iCount = iCount + 1
    Next
    fCountOccur = Format(iCount, "general number")
End Function

here's the function if anyone needs it, i got it from this thread

http://bytes.com/topic/access/answers/863128-count-specific-character-text

and then you can do this in a query for each letter

CountA: fCountOccur([facilityname],"A")

on that link there's many more pieces of code to do it in different ways, but for my purposes i think this one is best

 

[vbaInet]

Does that meet your requirements?

 

[lala]

it looks like it, i'm still working on details, but i think i'm good from here.
the function i posted for others who might run into the same problem

 

[lala]

thank you!!!!!

 

[vbaInet]

Here's something I just made up. Seems to work, but hasn't been tested thoroughly:

Public Function GetOccPercentage(strFirstText As String, strSecondText As String) As String
    Dim strLen As Integer, i As Integer, lenLonger As Integer, intMatch As Integer
    
    strLen = 0
    
    If Len(strFirstText) > Len(strSecondText) Then
        strLen = Len(strSecondText)
        lenLonger = Len(strFirstText)
    ElseIf Len(strSecondText) > Len(strFirstText) Then
        strLen = Len(strFirstText)
        lenLonger = Len(strSecondText)
    Else
        strLen = Len(strFirstText)
        lenLonger = Len(strFirstText)
    End If
    
    If strLen > 0 Then
        For i = 1 To strLen
            If Mid(strFirstText, i, 1) = Mid(strSecondText, i, 1) Then
                intMatch = intMatch + 1
            End If
        Next
    End If
    
    GetOccPercentage = Round((intMatch / lenLonger) * 100, 0) & " %"
End Function

You're welcome.

 

[lala]

this is 100% times better than what i have, thank you!!!!!!!!!!!!!!!!

 

[lala]

ok, this is definitely better because it does it in one step, but here

Expr1 FacilityName FacName
10 % Carondelet Holy Cross Hospital Holy Cross Hospital
67 % Carondelet Holy Cross Hospital Carondelet Holy Cross Swing Beds
67 % Carondelet Holy Cross Hospital Carondelet Holy Cross Swing Beds
67 % Carondelet Holy Cross Hospital Carondelet Holy Cross Swing Beds

i think this is wrong

it's something with the way you're calculating %, but other than that it's perfect, will see if i can fix it. can't believe you wrote this so fast

 

[lala]

ok, i got it, this won't work for me, i see what you're doing, you're taking the first letter and comparing it against the first letter in the second string, right?
this won't work for me, the reason why i'm doing this is because these names are similar but not exactly the same, spelling is different, word order is different, so i decided to count the occurences of all letters and compare the numbers.

thank you though, i can use some ideas from here

 

[vbaInet]

The way the function works is do it character by character, from start to finish. I think what you're trying to do is to count the occurence of one in the other.

If that's the case, then the IFs remain the same and in the check you use the InStr() function. Look that up. However, in the IFs you would need to identify which string is shorter than the other because in your check you need to check the shorter string against the longer string.

Good luck.

 

[vbaInet]

Well from your last post, checking letter by letter isn't a good way of checking whether both strings are the same. You should be checking word by word.

 

[lala]

Well from your last post, checking letter by letter isn't a good way of checking whether both strings are the same. You should be checking word by word.

I thought about that but sometimes the words are similar but not the same.
i know the instr(), i will try messing with your function to tailor it to what i need

also, can you help with this?

Public Function fCountOccur(strSource As String, strMatch As String) As String
Dim iCount As Long
Dim iPosition As Integer
    iCount = 0
    For iPosition = 1 To Len(strSource)
        If Mid(strSource, iPosition, 1) = strMatch Then iCount = iCount + 1
    Next
    'fCountOccur = Format(iCount, "general number")
    fCountOccur = iCount
End Function

for some reason the results come out as text, not numbers, and when i try to addthem up insted of
2+3+4=9 i get 234

i tried every way i can think of and can't get it

 

[vbaInet]

Have a look at the signature (the first line) of the function, it is returning a String. If you want it to return a number then you change it to return an Integer.

OR

You can use Val() function for each addition: Val(1) + Val(2) + Val(3)

 

[lala]

Have a look at the signature (the first line) of the function, it is returning a String. If you want it to return a number then you change it to return an Integer.

OR

You can use Val() function for each addition: Val(1) + Val(2) + Val(3)

Of course, the declarations
Everything is so easy once its pointed out and explained
And I didn't know about val

Thank you for all the lessons today

 

[vbaInet]

You're most welcome. At least you learnt a few things today

Here's the function I think you were referring to:

Public Function GetOccPercentage(strFirstText As String, strSecondText As String) As String
    Dim lenShorter As Integer, i As Integer, intMatch As Integer
    Dim strShort As String, strLong As String
    
    lenShorter = 0
    
    If Len(strSecondText) = 0 And Len(strFirstText) = 0 Then
        GetOccPercentage = "100 %"
        Exit Function
    ElseIf Len(strSecondText) = 0 Or Len(strFirstText) = 0 Then
        GetOccPercentage = "0 %"
        Exit Function
    End If
    
    If Len(strFirstText) > Len(strSecondText) Then
        lenShorter = Len(strSecondText)
        strShort = strSecondText
        strLong = strFirstText
    ElseIf Len(strSecondText) > Len(strFirstText) Then
        lenShorter = Len(strFirstText)
        strShort = strFirstText
        strLong = strSecondText
    Else
        lenShorter = Len(strFirstText)
        strShort = strFirstText
        strLong = strSecondText
    End If
    
    For i = 1 To lenShorter
        If InStr(1, strLong, Mid(strShort, i, 1)) Then
            intMatch = intMatch + 1
        End If
    Next

    GetOccPercentage = "Approx. " & Round((intMatch / lenShorter) * 100, 0) & " %"
End Function

 

[lala]

If you can give me a little more of your time

I got it to work but its not as good as I hoped it'd be
In the example I gave you earlier the holy cross hospital is the one that should've been picked (should've had a high percentage)
It got picked but the difference between it and the other was less than 1%, way too close, So that means my method doesn't work

What I'm doing is trying to pick out the best match out of several hospitals and it will be done automatically, so the method has to be almost perfect

Looking at this example, do you have any ideas? What method would pick out holy cross hospital

Thank you so much

 

[vbaInet]

Did you see my last post? That works for counting occurences.

I'm not clear on exactly what you want to check. Give some examples and explain using those.

 

[vbaInet]

Here's a another variation. Just added one or two things:

Public Function GetOccPercentage(strFirstText As String, strSecondText As String) As String
    Dim lenShorter As Integer, lenLonger As String, i As Integer, intMatch As Integer
    Dim strShort As String, strLong As String
    
    lenShorter = 0
    
    If Len(strSecondText) = 0 And Len(strFirstText) = 0 Then
        GetOccPercentage = "100 %"
        Exit Function
    ElseIf Len(strSecondText) = 0 Or Len(strFirstText) = 0 Then
        GetOccPercentage = "0 %"
        Exit Function
    End If
    
    If Len(strFirstText) > Len(strSecondText) Then
        lenShorter = Len(strSecondText)
        lenLonger = Len(strFirstText)
        strShort = strSecondText
        strLong = strFirstText
    ElseIf Len(strSecondText) > Len(strFirstText) Then
        lenShorter = Len(strFirstText)
        lenLonger = Len(strSecondText)
        strShort = strFirstText
        strLong = strSecondText
    Else
        lenShorter = Len(strFirstText)
        lenLonger = Len(strSecondText)
        strShort = strFirstText
        strLong = strSecondText
    End If
    
    For i = 1 To lenShorter
        If InStr(i, strLong, Mid(strShort, i, 1)) Then
            intMatch = intMatch + 1
        End If
    Next

    GetOccPercentage = "Approx. " & Round((intMatch / lenLonger) * 100, 0) & " %"
End Function