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

 

[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...

[table="width: 500"]
[tr]
	[td]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[/td]
[/tr]
[/table]

One thing I forgot to mention about the above code that I posted... it maintains the original letter casing, so when you do your comparison, you should probably make the returned values all the same case so that you do not accidentally get tripped up with a word starting a sentence in one cell (hence its first letter would be upper case) and not starting a sentence in the other cell (hence its first letter would be lower case. So, instead of using the formula I show above, you should use this one intead...

=LOWER(SortWords(A1))=LOWER(SortWords(B1))

Also, if you do not want to use the generalized function above, but would rather use a function that does the comparison internally like Tetra201's function does and which requires it to be called like this instead...

=CompareWords(A1,B1)

then you can use this function (a modification of my original function) instead...

[table="width: 500"]
[tr]
	[td]Function CompareWords(ByVal S1 As String, ByVal S2 As String) As Boolean
  Dim X As Long, AL1 As Object, AL2 As Object, Data1() As String, Data2() As String
  S1 = Replace(LCase(S1), " - ", " ")
  S2 = Replace(LCase(S2), " - ", " ")
  For X = 1 To Application.Max(Len(S1), Len(S2))
    If Mid(S1, X, 1) Like "[!A-Za-z0-9-]" Then Mid(S1, X) = " "
    If Mid(S2, X, 1) Like "[!A-Za-z0-9-]" Then Mid(S2, X) = " "
  Next
  Data1 = Split(Application.Trim(S1) & " ", " ")
  Data2 = Split(Application.Trim(S2) & " ", " ")
  If UBound(Data1) = UBound(Data2) Then
    Set AL1 = CreateObject("System.Collections.ArrayList")
    Set AL2 = CreateObject("System.Collections.ArrayList")
    For X = 0 To UBound(Data1)
      AL1.Add Application.Trim(Data1(X))
      AL2.Add Application.Trim(Data2(X))
    Next
    AL1.Sort
    AL2.Sort
  End If
  If Not AL1 Is Nothing Then CompareWords = Join(AL1.ToArray, ",") = Join(AL2.ToArray, ",")
End Function[/td]
[/tr]
[/table]

 

[MissMiniExcel]

Hello Rick,

Thanks for solving my Excel vba problem and sharing your knowledge.

I like that it returned a comma-delimited list.

Keep Exceling! (haha just thought of this word)

Happy New Year!
missminiexcel

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