CODEWITHSUNDEEP
vbaexcelcomparecells
zzaewon
zzaewon

Reputation: 29

Excel/how to compare characters in the cells

I would like to compare characters in the cells and return the remain. For example,

A1 = (111, 222, 444, 555)

B1 = (111, 222, 333, 444, 555, 666)

Then, I would like to see the remains in the cell C1,

C1 = (333, 666)

Is there any way I can do with excel functions? Do I have to do this with VBA code? Thanks in advance!

Upvotes: 1

Views: 1875

Answers (1)

cyboashu
cyboashu

Reputation: 10433

Building on @Tim's comment, one possible UDF could look like this.

Public Function GetDiff(rngFirst As Range, rngSecond As Range) As String

    Dim arrFirst
    Dim arrSecond

    Dim lRctr1 As Long
    Dim lRctr2 As Long

    Dim bMatch      As Boolean
    Dim strResult   As String

    arrFirst = Split(IIf(Len(rngFirst) >= Len(rngSecond), rngFirst, rngSecond), ",")
    arrSecond = Split(IIf(Len(rngFirst) <= Len(rngSecond), rngFirst, rngSecond), ",")

    For lRctr1 = LBound(arrFirst) To UBound(arrFirst)
    bMatch = False
        For lRctr2 = LBound(arrSecond) To UBound(arrSecond)
            If (arrFirst(lRctr1) = arrSecond(lRctr2)) Then
                lRctr2 = UBound(arrSecond) + 1
                bMatch = True
            End If
        Next

        If Not bMatch Then
            strResult = strResult & arrFirst(lRctr1) & ","
        End If
    Next

    If Len(strResult) > 0 Then
        strResult = Mid(strResult, 1, Len(strResult) - 1)
    End If

    GetDiff = strResult

End Function

Just add the above code in a module and then you can use it like this in let's say cell C1 =GetDiff(A1,B1)

Upvotes: 1

Related Questions