Compare JSON data and splitting into corrected codes, deleted codes and added codes

chandramouliarun,

This is how I interpret the problem and my result is slightly different from yours.

Sub test()
    Dim a, e, i As Long, ii As Long, dic As Object, m
    Set dic = CreateObject("Scripting.Dictionary")
    a = Sheets("input ").Cells(1).CurrentRegion.Value
    For i = 2 To UBound(a, 1)
        If Not dic.exists(a(i, 1)) Then
            Set dic(a(i, 1)) = CreateObject("Scripting.Dictionary")
            dic(a(i, 1))(a(1, 1)) = a(i, 1)
            dic(a(i, 1))(a(1, 2)) = a(i, 2)
            dic(a(i, 1))("codername") = a(i, 4)
            GetDetails a(i, 3), a(i, 1), dic, True
        Else
            GetDetails a(i, 3), a(i, 1), dic, False
        End If
    Next
    With Sheets("output").Cells(1).CurrentRegion
        .Offset(1).ClearContents
        For i = 0 To dic.Count - 1
            For ii = 1 To .Columns.Count
                .Cells(i + 2, ii) = dic.items()(i)(.Cells(1, ii).Value)
            Next
        Next
    End With
End Sub

Private Sub GetDetails(ByVal txt As String, myKey, dic As Object, flg As Boolean)
    Dim m As Object
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = """description"": *""(.+?)"".*[\r\n]+.*""dCode"": *""(.+?)"""
        For Each m In .Execute(txt)
            If flg Then
                dic(myKey)(m.submatches(0)) = m.submatches(1)
            Else
                If dic(myKey).exists(m.submatches(0)) Then
                    If dic(myKey)(m.submatches(0)) <> m.submatches(1) Then
                        dic(myKey)("correctedcode") = dic(myKey)("correctedcode") & _
                        IIf(dic(myKey)("correctedcode") <> "", ", ", "") & m.submatches(1)
                        dic(myKey)("deletedcode") = dic(myKey)("deletedcode") & _
                        IIf(dic(myKey)("deletedcode") <> "", ",", "") & dic(myKey)(m.submatches(0))
                        dic(myKey)(m.submatches(0)) = m.submatches(1)
                    End If
                Else
                    dic(myKey)("addedcode") = m.submatches(1)
                End If
            End If
        Next
    End With
End Sub