Excel Macro or Functions to Read Mismatch string between two cells

Here's the UDF for you:

Option Explicit

Function CompareLists(Rng1 As Range, Rng2 As Range, Optional Delim As String) As String
Dim Arr As Variant, buf As String, n As Long

If Delim = "" Then Delim = ","
Arr = Split(Rng1.Cells(1), Delim)

For n = LBound(Arr) To UBound(Arr)
    If InStr(Rng2, Arr(n)) = 0 And Len(Arr(n)) > 0 Then buf = buf & "," & Arr(n)
Next n

Arr = Split(Rng2.Cells(1), Delim)

For n = LBound(Arr) To UBound(Arr)
    If InStr(Rng1, Arr(n)) = 0 And Len(Arr(n)) > 0 Then buf = buf & "," & Arr(n)
Next n

If Len(buf) > 0 Then CompareLists = Mid(buf, 2, Len(buf)) Else CompareLists = "All match"

End Function

Put it into a standard module (Insert > Module), then use in a cell like so:

=CompareLists(A1, B1, ",")

You can use any delimiter you want in the 3rd paramater, if you leave it out it will default to comma.

Excellent Thanks its solved my problem.

Regards,
Venkateswaran K



MODERATOR'S NOTE: Please do not quote entire posts into your posts. Thanks.

Function GetDiff(txt1 As String, txt2 As String) As String
    Dim e, a(1 To 3) As String
    Static dic As Object
    If dic Is Nothing Then Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    dic.RemoveAll
    For Each e In Split(txt1, ",")
        If Trim$(e) <> "" Then
            dic(Trim$(e)) = Empty
        End If
    Next
    For Each e In Split(txt2, ",")
        If Trim$(e) <> "" Then
            If dic.exists(Trim$(e)) Then
                a(2) = a(2) & IIf(a(2) = "", "", ", ") & Trim$(e)
                dic.Remove Trim$(e)
            Else
                a(3) = a(3) & IIf(a(3) = "", "", ", ") & Trim$(e)
            End If
        End If
    Next
    a(1) = Join$(dic.keys, ", ") & ", " & a(3)
    GetDiff = a(1)
End Function

don't ask me comments, it's not mine

I have marked this thread solved for you.
In the future please select Thread Tools from the menu above and mark the thread as solved. Thanks.