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

 

[kelly mort]

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

I have removed the application. Trim and used only the trim but still same results

 

[JoeMo]

I have removed the application. Trim and used only the trim but still same results

Application.Trim is better in case there are extra internal spaces. I would do the trim when you initially set the variables. For example, this line:

arr(1) = freg2.Text: V1 = Split(.Trim(arr(1)), " ")

would become:

arr(1) = .Trim(freg2.Text): V1 = Split(arr(1), " ")

 

[kelly mort]

I think there is a big problem with the loop I added to the modification I made. As I tried to fix the problem the hard way, I came up with this test. So when I place one name in cell A2 and then more other names in same column and the last cell in column A takes a different name than the one in cell A2, I get "Words do not match". Then when I make the name in the last cell in column A same as the one in cell A2, I get "Same words in both strings".


Now you can see that I am not exiting the loop so it runs to the last used row in column A. I found out that the problem I was facing before was due to me exiting the loop after testing for equal length of the two strings, which was not properly done – as I think.


Now, based on the frame or layout of the code I have here, if I can get a way to loop properly through all the cells in column A for the match, I am very sure my nightmare will vanish. I did the best I could in the first place thinking I solved that challenge, but now that I have come across the bug, I don’t know any other way to resolve this.


So I need bigger minds to help me out. I know there is always a way out. Thanks to you all once again.

Sub kelly()
Dim Arr(1 To 2), i As Long, Matches As Long
Dim V1 As Variant, V2 As Variant
Arr(1) = [A2].Value
Dim sName
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For Each sName In Range("A2:A" & lr).Cells
        If Len(Trim(Arr(1))) = Len(Trim(sName.Text)) Then
            Arr(2) = sName.Text
        End If
    Next sName


    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)
        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

 

[JoeMo]

After going offline earlier today, I realized that I hadn't studied your code in post #5 carefully. The code I gave you to adapt compared the words in two cells, reporting back whether they matched or not, and then the routine ended. The variable "Matches" was part of this comparison.

In looking at your adaptation of that code, I see now that you are comparing one string of words to more than one other string of words sequentially. In that case, it is essential that, after each comparison of a pair of strings, you reset the variable "Matches" before the next pair is compared. Your code fails to do that, and as you have observed, produces some strange results. Try your code from post #5 again after you insert this one line: Matches = 0 just before the: Next sName line.

 

[kelly mort]

Yesssss!!!!

I knew there was a way out of this and something I was missing.

You untouchable, @JoeMo. It worked brilliantly

I am very grateful for your time with me on this thread.

 

[JoeMo]

Yesssss!!!!

I knew there was a way out of this and something I was missing.

You untouchable, @JoeMo. It worked brilliantly

I am very grateful for your time with me on this thread.

You are welcome - thanks for the reply.