Compare two cell if test exist in any order

[MissMiniExcel]

Greetings from snowing Colorado.

Case -
Cell 1 I love winter time especially when skiing.
Cell 2 I especially love winter when time skiing.

The same text exist in Cell 1 and Cell 2.

What formula or vba will solution case?

Thanks in advacne for your sharing your Excel expertise!

MissMiniExcel

 

[AliGW]

You are going to have to be much more precise in your requirements! What EXACTLY do you mean by "the same text"? Is it just one word ("skiing")? Will you be searching specifically for this word or just trying to find strings containing one or more matching words?

 

[MissMiniExcel]

Hello AliGGW,

Thanks for your response and sharing your knowledge.

The same words exist in both cells, however they are not in the same sequence.

Example, sentence has 6 words, but not in same sequence.
cell 1: I love the first sight of snow
cell 2: I love sight the snow of first

You are going to have to be much more precise in your requirements! What EXACTLY do you mean by "the same text"? Is it just one word ("skiing")? Will you be searching specifically for this word or just trying to find strings containing one or more matching words?

Thanks,
Myrna

 

[AliGW]

So all of the words in the string have to appear in both cells - yes?

 

[Tetra201]

@MissMiniExcel:

See if the following UDF (User Defined Function) works for you.
Example of usage: =CmpWrd(A1,A2); it returns TRUE or FALSE.

Function CmpWrd(ByVal S1, S2 As String) As Boolean
    Dim S(1 To 2) As String
    S(1) = S1: S(2) = S2
    For i = 1 To 2 Step 1
        For Each c In Array(".", ",", ":", ";""'", """", "-", "_", "/", "\", "(", ")", "!", "?")
            S(i) = Replace(S(i), c, "")
        Next c
        S(i) = UCase(WorksheetFunction.Trim(S(i)))
    Next i
    CmpWrd = True
    A1 = Split(S(1), " "): A2 = Split(S(2), " ")
    If UBound(A1) <> UBound(A2) Then CmpWrd = False: Exit Function
    For i = LBound(A1) To UBound(A1) Step 1
        wdFound = False
        For j = LBound(A2) To UBound(A2) Step 1
            If A1(i) = A2(j) Then wdFound = True: Exit For
        Next j
        If Not wdFound Then CmpWrd = False: Exit Function
    Next i
End Function

 

[MissMiniExcel]

Yes, all words have to appear in both cells.

The words do not have to appear in the same other.

I am going to try Tetra201 code.

Keep you posted on the results.

Thanks a tons for your help!
Myrna

So all of the words in the string have to appear in both cells - yes?

 

[MissMiniExcel]

Hello Tetra,

Yes, the User Defined Function worked flawlessley.

Thank you for sharing your knowledge.

MissMiniExcel

@MissMiniExcel:

See if the following UDF (User Defined Function) works for you.
Example of usage: =CmpWrd(A1,A2); it returns TRUE or FALSE.

Function CmpWrd(ByVal S1, S2 As String) As Boolean
    Dim S(1 To 2) As String
    S(1) = S1: S(2) = S2
    For i = 1 To 2 Step 1
        For Each c In Array(".", ",", ":", ";""'", """", "-", "_", "/", "\", "(", ")", "!", "?")
            S(i) = Replace(S(i), c, "")
        Next c
        S(i) = UCase(WorksheetFunction.Trim(S(i)))
    Next i
    CmpWrd = True
    A1 = Split(S(1), " "): A2 = Split(S(2), " ")
    If UBound(A1) <> UBound(A2) Then CmpWrd = False: Exit Function
    For i = LBound(A1) To UBound(A1) Step 1
        wdFound = False
        For j = LBound(A2) To UBound(A2) Step 1
            If A1(i) = A2(j) Then wdFound = True: Exit For
        Next j
        If Not wdFound Then CmpWrd = False: Exit Function
    Next i
End Function

 

[MissMiniExcel]

AliGW,

Tetra's suggested Excel Function worked!

Thanks again for helping.

Have a fab week,
MissMiniExcel

 

[Rick Rothstein]

I have a different approach for your problem. I created a function which sorts all of the words in the text passed to it and returns a comma-delimited list (the delimiter is easily changed in the next-to-last line of code) which I thought could possibly be useful in other applications one may create in the future. To use my function, simply compare the output from it for the two cells you want to check. For example...

=SortWords(A1)=SortWords(B1)

Here is the code for my function...

Function SortWords(ByVal Text As String) As String
  Dim X As Long, Data() As String
  Text = Replace(Text, " - ", " ")
  For X = 1 To Len(Text)
    If Mid(Text, X, 1) Like "[!A-Za-z0-9-]" Then Mid(Text, X) = " "
  Next
  Data = Split(Application.Trim(Text), " ")
  With CreateObject("System.Collections.ArrayList")
    For X = 0 To UBound(Data)
      .Add Application.Trim(Data(X))
    Next
    .Sort
    SortWords = Join(.ToArray, ", ")
  End With
End Function

 

[Tetra201]

Hello Tetra,
Yes, the User Defined Function worked flawlessley.
Thank you for sharing your knowledge.
MissMiniExcel

You are welcome.