Compare two columns of data, not exact order

[kswny0775]

I am looking to compare two columns of data to see if they contain the same data regardless of order.

Example:

A1: Apple; Orange; Cherry
B2: Apple; Cherry; Orange
Match -- both contain all the same values, but not in the same order

A2: Cherry; Orange
B2: Cherry; Apple
No Match -- both do not contain ALL the same values

 

[rlv01]

You could do it with a UDF

Book1
	A	B	C
1	Apple; Orange; Cherry	Apple; Cherry; Orange	Match
2	Cherry; Orange	Cherry; Apple	No Match
3	Cherry; Orange	Orange; Cherry	Match
4	Cherry	Cherry; Cherry; Cherry	No Match
5	Cherry	Cherry	Match
6	Apple; Orange; Cherry	Apple; Cherri; Orange	No Match
Sheet8

Cell Formulas
Range	Formula
C1:C6	C1	=compare(A1,B1)

Function Compare(S1 As String, S2 As String) As String
    Dim IsEqual As Boolean
    Dim SD As Object
    Dim SA1 As Variant, SA2 As Variant, X As Variant

    Set SD = CreateObject("Scripting.dictionary")
    SA1 = Split(Replace(S1, " ", ""), ";")
    SA2 = Split(Replace(S2, " ", ""), ";")

    If UBound(SA1) <> UBound(SA2) Then
        IsEqual = False
    Else
        IsEqual = True
        For Each X In SA1
            If Not SD.exists(X) Then
                SD.Add X, ""
            End If
        Next X
        For Each X In SA2
            If Not SD.exists(X) Then
                IsEqual = False
                Exit For
            End If
        Next X
    End If

    If IsEqual Then
        Compare = "Match"
    Else
        Compare = "No Match"
    End If
    Set SD = Nothing
End Function

 

[ironny90]

this code is awesome. thank you!

 

[rlv01]

You're welcome.

 

[kswny0775]

I am not familiar with VBA. Sorry. There is no formula for it?