Compare two cells with Text strings separated by comma; return Unique & Duplicate values

[pvkvimalan]

Hi,

I have vast data flowing from Primavera P6. There are numerous Activity IDs' that has to be compared between two schedules. Each cell has several text strings (Activity ID) that are separated by a comma (,). I have recreated the scenario in the below table and require 3 different results as in the table. A simple substitute function is able to produce accurate results. I did try some VBA's but the results were partial. I need some expert advice to help solving the problem.

Thanks in advance

 

[Akuini]

Here's another way:
The data is in col B:C & the result is in col D:F

Sub a1116279a()
'https://www.mrexcel.com/board/threads/compare-two-cells-with-text-strings-separated-by-comma-return-unique-duplicate-values.1116279/#post-5378800

Dim i As Long
Dim tx1 As String, tx2 As String, tx3 As String
Dim va, vb, ary, arz, z, res


va = Range("B2:C" & Cells(Rows.count, "B").End(xlUp).Row)
ReDim vb(1 To UBound(va, 1), 1 To 3)
For i = 1 To UBound(va, 1)
    ary = Split(va(i, 1), ",")
    arz = Split(va(i, 2), ",")
    tx1 = "": tx2 = "": tx3 = ""
    
    For Each z In ary
        res = Application.Match(z, arz, 0)
        If IsNumeric(res) Then
            tx3 = tx3 & "," & arz(res - 1)
            arz(res - 1) = ""
        Else
            tx1 = tx1 & "," & z
        End If
    Next
    
    For Each z In arz
        If Len(z) Then tx2 = tx2 & "," & z
    Next

vb(i, 1) = Mid(tx1, 2)
vb(i, 2) = Mid(tx2, 2)
vb(i, 3) = Mid(tx3, 2)

Next

Range("D2").Resize(UBound(vb, 1), 3) = vb

End Sub

 

[pvkvimalan]

Thanks, @Paul Ked, @Yongle, @Akuini for your swift response & efforts to help me. I replied a message yesterday, that the code provided by @Yongle was working and the thread can be closed as "SOLVED", but it's not been posted. Also, I'm interested to learn the VBA Coding, I need any guidance as to how to start and if any online course or reference is available it will be of great help.

 

[Paul Ked]

You're welcome
There are many courses out there, in different languages too. This is a great introduction to it though
Excel VBA Tutorial for Beginners: Learn in 3 Days

 

[pvkvimalan]

You're welcome
There are many courses out there, in different languages too. This is a great introduction to it though
Excel VBA Tutorial for Beginners: Learn in 3 Days

Thank You Paul