Compare two cell if test exist in any order

MissMiniExcel

New Member
Joined
Dec 15, 2016
Messages
6
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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?
 
Upvote 0
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
 
Upvote 0
@MissMiniExcel:

See if the following UDF (User Defined Function) works for you.
Example of usage: =CmpWrd(A1,A2); it returns TRUE or FALSE.
Code:
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
 
Upvote 0
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?
 
Upvote 0
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.
Code:
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
 
Upvote 0
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...
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top