Finding words match in a string or an array

[kelly mort]

I don't want to break any rules here. And if I am doing so then I am sorry.

I have posted a quest here https://www.mrexcel.com/forum/excel-questions/1109916-vba-code-compare-contents-two-textboxes.html

Which looks like what I am posting again. But this seems a bit clearer. After re-looking at the problem carefully, I decided to shorten my request so that I may get the right help.

Though I don't really understand what's going on in the "Application. Match " function, a little probe made me realise I may be missing something vital.


So here I am only interested in matches . all words match.

If one list is: "Small Yellow Car"

And the other is : "Car Small Yellow" or any of the combination.

Like I pointed out to the above link, I don't really know how the VBA match is doing the comparison but since it's not giving me the result, I think there should be another way.

There were so many instances where it gave me a match where it shouldn't.

I have been trying to fix this for the past few days but not through yet.

Always I will like to verify equal length before doing the comparison.

Thanks for taking time to look at this for me. Thanks

 

[JoeMo]

Maybe you can adapt this for your text box comparison.

Sub kelly()
'tests whether the words of a space-delimited string in cell A2 match
'those of a space-delimited string in A3
Dim Arr(1 To 2), i As Long, Matches As Long
Dim V1 As Variant, V2 As Variant
For i = 1 To 2
    Arr(i) = Cells(i + 1, "A").Value
Next i
If Len(Application.Trim(Arr(1))) <> Len(Application.Trim(Arr(2))) Then
    MsgBox "Strings are not of same length - exiting sub"
    Exit Sub
End If
V1 = Split(Application.Trim(Arr(1)), " ")
V2 = Split(Application.Trim(Arr(2)), " ")
For i = LBound(V1) To UBound(V1)
    For j = LBound(V2) To UBound(V2)
        'remove the Lcase function in the next line if you want to employ case sensitivity
        If LCase(V1(i)) = LCase(V2(j)) Then
            Matches = Matches + 1
            If Matches = UBound(V1) + 1 Then
                MsgBox "Same words in both strings"
                Exit Sub
            End If
        End If
    Next j
Next i
MsgBox "Words do not match"
End Sub

 

[kelly mort]

Maybe you can adapt this for your text box comparison.

Sub kelly()
'tests whether the words of a space-delimited string in cell A2 match
'those of a space-delimited string in A3
Dim Arr(1 To 2), i As Long, Matches As Long
Dim V1 As Variant, V2 As Variant
For i = 1 To 2
    Arr(i) = Cells(i + 1, "A").Value
Next i
If Len(Application.Trim(Arr(1))) <> Len(Application.Trim(Arr(2))) Then
    MsgBox "Strings are not of same length - exiting sub"
    Exit Sub
End If
V1 = Split(Application.Trim(Arr(1)), " ")
V2 = Split(Application.Trim(Arr(2)), " ")
For i = LBound(V1) To UBound(V1)
    For j = LBound(V2) To UBound(V2)
        'remove the Lcase function in the next line if you want to employ case sensitivity
        If LCase(V1(i)) = LCase(V2(j)) Then
            Matches = Matches + 1
            If Matches = UBound(V1) + 1 Then
                MsgBox "Same words in both strings"
                Exit Sub
            End If
        End If
    Next j
Next i
MsgBox "Words do not match"
End Sub

okay. It's has passed a few tests so far. I am now about to adapt it to my usage. When I get stacked in the process I will come back here.

Until then, I am very grateful .

Thanks so much

 

[JoeMo]

okay. It's has passed a few tests so far. I am now about to adapt it to my usage. When I get stacked in the process I will come back here.

Until then, I am very grateful .

Thanks so much

You are welcome - thanks for the reply.

 

[kelly mort]

@JoeMo and all those who see this and can help me out.



Like I said before, when I get stucked again, I will come here. I was testing the code two days ago I came to realized something. This is how I used the code in my workbook. I am using this under my edit button.

    Dim DupName$, DupClass$, wStatus&, lr&
    Dim arr(1 To 2), Matches&, V1, V2, sName
    Dim FoundID$
    wStatus = 0


lr = Sheets(“Sheet1”).Cells(Rows.Count, "CA").End(xlUp).Row
        arr(1) = freg2.Text: V1 = Split(.Trim(arr(1)), " ")
    For Each sName In Sheets((“Sheet1”).Range("CA7:CA" & lr).Cells
        If Len(.Trim(arr(1))) = Len(.Trim(sName.Text)) Then
            arr(2) = sName.Text: V2 = Split(.Trim(arr(2)), " ")
            For i = LBound(V1) To UBound(V1)
                For j = LBound(V2) To UBound(V2)
                    If LCase(V1(i)) = LCase(V2(j)) Then
                        Matches = Matches + 1
                        If Matches = UBound(V1) + 1 Then
                            wStatus = 1
                            DupName = sName.Text
                            FoundID = sName.Offset(, -1).Text
                            Exit For
                        End If
                    End If
                Next j
            Next i
        End If
    Next sName


If wStatus = 1 Then
         If Len(FoundID) And FoundID <> freg1.Text Then
             MsgBox freg2 & " already exists in as " & DupName, vbExclamation, "Duplicate alert"
            freg2.SetFocus
            Exit Sub
        End If
End If

1. TEMENG SAMUEL BOATENG

2. OSEI BOATENG RICHMOND


So in my database I have these two names that I came into contact with and the above code is telling me both names are the same which should not be the case. The name “TEMENG SAMUEL BOATENG” comes before the name “OSEI BOATENG RICHMOND”. So when the name “OSEI BOATENG RICHMOND” appears inside the textbox, it tells me the name “OSEI BOATENG RICHMOND” is same as “TEMENG SAMUEL BOATENG”. So I decided to exchange the position of the two names and saw that if the names are of equal length and the one in the textbox happens to be placed before the other, then I pass the test and no alert is displayed. However, when the name in the textbox happens to be placed after the other, then I fail the test and the alert is shown. What is it that I am doing wrongly? And how can I fix this?

 

[JoeMo]

If you put the two names in cells A2 & A3 in a new sheet and run the code I posted in post #2 , they are not the same. If you reverse the order of the two names in those cells and run the code again, they are not the same. So, the original code is correct.

So, the issue is within the modifications you made to the code. I can only guess that when your modified code says they are the same, the value in arr(1) is the same as the instance of sName which you are assigning to arr(2). Step through the code to see if this guess is correct. If so, modify the code to ensure this doesn't happen.

 

[kelly mort]

I have stepped through the code and the arr(1) and arr(2) are always not the same.

I think it is that "BOATENG" that makes it think they names are same.

Because I tried a modification if the BOATENG in the name "OSEI BOATENG RICHMOND" to "OSEI BOATENF RICHMOND".

Then this time around, the message alert showed this:

OSEI BOATENF RICHMOND already exists as OSEI KONADU BENEDICTA

Now, it is the OSEI!!

I have read the lines I posted above several times. I see everything cool yet I don't know why it's not working cool for me.

 

[JoeMo]

I can't see your workbook so it's a bit difficult to try and diagnose your problem, let alone resolve it. From the code you posted I see a couple lines that have a dot (".") where perhaps there shouldn't be. What happens if you remove those dot characters (in bold red below)?

If Len(.Trim(arr(1))) = Len(.Trim(sName.Text)) Then
         arr(2) = sName.Text: V2 = Split(.Trim(arr(2)), " ")

 

[kelly mort]

Those periods are there because I used

"With Application" to envelope them.

 

[JoeMo]

Those periods are there because I used

"With Application" to envelope them.

OK, you didn't post that part. Have you looked at the possibility that Application.Trim is not being used consistently?