Finding the Added and Deleted Codes out of the two sets of Codes Entered

see if this works for you:

Sub t()
Dim c As Range, spl As Variant, i As Long, txt As String
    For Each c In Range("A2", Cells(Rows.Count, 1).End(xlUp))
        spl = Split(c.Value, ",")
        For i = LBound(spl) To UBound(spl)
            If InStr(c.Offset(, 1).Value, spl(i)) = 0 Then
                txt = txt & ", " & spl(i)
            End If
        Next
        If txt <> "" Then
            c.Offset(, 2) = Right(txt, Len(txt) - 2)
        End If
        txt = ""
        spl = Split(c.Offset(, 1).Value, ",")
        For i = LBound(spl) To UBound(spl)
            If InStr(c.Value, spl(i)) = 0 Then
                txt = txt & ", " & spl(i)
            End If
        Next
        If txt <> "" Then
            c.Offset(, 3) = Right(txt, Len(txt) - 2)
        End If
        txt = ""
    Next
End Sub

Originally Posted by chandramouliarun

@JLGWhiz:Appreciate your response!..Can you please provide me with your excel sheet ...

I used yours in the linked file.

Naughty you!! You changed the attachment after posting!!

The 908/U908 issue caused some problems, for a while...

=IF($G2="","",SUMPRODUCT((($A$2:$A$6=H$1)*(LEN("¦"&SUBSTITUTE($B$2:$C$6,",","¦"))-(LEN(SUBSTITUTE("¦"&SUBSTITUTE($B$2:$C$6,",","¦"),"¦"&$G2,"")))))/LEN("¦"&$G2)))

copied across and down.

Ooops. And I was naughty, too. here's the file...

Or, as per your parallel post, not sorted by coder:


=IF($G2="","",SUMPRODUCT(((LEN("¦"&SUBSTITUTE($B$2:$C$6,",","¦"))-(LEN(SUBSTITUTE("¦"&SUBSTITUTE($B$2:$C$6,",","¦"),"¦"&$G2,"")))))/LEN("¦"&$G2)))

and the file, for not sorted by coder.

Chandramoulian. Re-read the forum rules, especially Rule 5.

5. Do not open more than ONE thread per issue here. Expect duplicates to be closed.

You have made 3 threads on, essentially, the same problem. I have closed one of them and merged the other two (both of which have had replies).

Please DO NOT duplicate threads again. It makes everything VERY messy.

So do any/all of the offered appriaches work, or are modifications needed?

Hi Glenn, For the file not sorted by order and also for the file sorted by coder..Is it possible to automatically total list of distinct code column from the deleted code and the add code column ,instead of manually entering the code column ... Otherwise this works awesome!!


Thanks,
Arun

I added one helper column (column H) to split up the concatenated coder IDs:

=TRIM(MID(SUBSTITUTE(","&CONCAT($B$2:$B$6&","&$C$2:$C$6&","),",",REPT(" ",125)),125*ROWS($1:1),125))

and a formula in I2 to return the unique codes:
=IFERROR(INDEX(H:H,AGGREGATE(15,6,ROW($H$2:$H$21)/(($H$2:$H$21<>"")*(COUNTIF(I$1:I1,$H$2:$H$21)=0)),1)),"")

and a formula in J1 to return the unique coders:
=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$10)/(($A$2:$A$10<>"")*(COUNTIF($I1:I1,$A$2:$A$10)=0)),1)),"")

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Originally Posted by chandramouliarun

@Glenn!..This is so awesome..I am just waiting for one more reply JLGWhiz to close this thread!..This forum is very helpful!

If you have duplicates in one or the other categories and only one of the duplicates is removed while that same value is still in the other category, then the code was not removed, only the duplicate was removed. I don't know how to tell if a duplicate is removed without doing some complex coding that I don't have an inclination to do.
Regards, JLG