Need help with VB Macro to compare 2 worksheets

[kalyanr12]

Experts,

Attached is an excel document that contains 2 worksheets named "Structure_DATA" and "XML_DATA". The requirement is that I have to compare data in these 2 worksheets and report the differences in a new worksheet (of same workbook) named "Structure_XML_Comparison".

If you look at the Structure_DATA and XML_DATA worksheets, the columns are common ( 5 in number), but the data in the rows are not the same. Here, "Name" column is the primary key to compare. I need a macro to do the following:
1. If the data in the "Name" column of XML_DATA worksheet does not exist in the Structure_DATA worksheet, get that specific row(s) from XML_DATA and report it as "Added_Items" in the comparison sheet (on the XML_DATA - Right hand side). Refer to the format in the attached excel doc.
2. If the data in the "Name" column of Structure_DATA worksheet does not exist in the XML_DATA worksheet, get that specific row(s) from Structure_DATA and report it as "Removed_Items" in the comparison sheet (on the Structure_DATA- Left hand side).
3. If the data in all the columns of both the worksheets is exactly same, then report it as "Unchanged_Items". Show the matched rows on both Left and Right hand sides.
4. If the data is different in any of the columns of both the worksheets, then the specific value which is different needs to be highlighted in red on both sides. All the modified or updated rows, should be reported under "Modified_Items"

I am not comfortable with the Macros, so will need help from the experts to get the macro logic for the above. Appreciate your help in advance.

[kalyanr12]

This is urgent. Appreciate any quick response. Thanks a bunch.

[kalyanr12]

Second try... Please help me with the macro.

[PCI]

For a first trial see next code, some comestic adjustments need to be done , but first comment

Option Explicit

Sub CheckData()
Dim ObjDic1  As Object
Dim ObjDic2  As Object
Dim ObjDic11  As Object
Dim ObjDic22  As Object
Dim F As Range
Dim TEMP
Dim LastRow As Long, LastRow1 As Long, LastRow2 As Long
Dim G
Dim AAA, BBB

    Set ObjDic1 = CreateObject("Scripting.Dictionary")
    Set ObjDic11 = CreateObject("Scripting.Dictionary")
    Set ObjDic2 = CreateObject("Scripting.Dictionary")
    Set ObjDic22 = CreateObject("Scripting.Dictionary")

    LastRow = 5: LastRow1 = 5: LastRow2 = 5
'-----  DICTIONARY  PREPARATION
    With Sheets("Structure_DATA")
        For Each F In Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
            ObjDic1.Item(F.Value) = F.Resize(1, 5)
            TEMP = F & F.Offset(0, 1) & F.Offset(0, 2) & F.Offset(0, 3) & F.Offset(0, 4)
            ObjDic11.Item(TEMP) = F.Resize(1, 5)
        Next F
    End With
    With Sheets("XML_DATA")
        For Each F In Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
            ObjDic2.Item(F.Value) = F.Resize(1, 5)
            TEMP = F & F.Offset(0, 1) & F.Offset(0, 2) & F.Offset(0, 3) & F.Offset(0, 4)
            ObjDic22.Item(TEMP) = F.Resize(1, 5)
        Next F
    End With
'-----  ADDED  ITEM
    With Sheets("Structure_DATA")
        For Each F In Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
            If (ObjDic2.exists(F.Value)) Then ObjDic2.Remove (F.Value)
        Next F
            Sheets("Structure_XML_Comparison").Cells(LastRow, 1) = "Added Items"
            Sheets("Structure_XML_Comparison").Cells(LastRow, 8).Resize(ObjDic2.Count, 5) = Application.Transpose(Application.Transpose(ObjDic2.Items))
            LastRow = LastRow + ObjDic2.Count + 1
    End With
'-----  REMOVED  ITEMS
    With Sheets("XML_DATA")
        For Each F In Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
            If (ObjDic1.exists(F.Value)) Then ObjDic1.Remove (F.Value)
        Next F
            Sheets("Structure_XML_Comparison").Cells(LastRow, 1) = "Removed Items"
            Sheets("Structure_XML_Comparison").Cells(LastRow, 2).Resize(ObjDic1.Count, 5) = Application.Transpose(Application.Transpose(ObjDic1.Items))
            LastRow = LastRow + ObjDic1.Count + 1
    End With

'-----  UNCHANGED  ITEMS
    ObjDic2.RemoveAll
    For Each G In ObjDic11
        If Not (ObjDic22.exists(G)) Then
            ObjDic2.Item(Application.Index(ObjDic11.Item(G), 1, 1)) = ObjDic11.Item(G)
            ObjDic11.Remove (G)
        End If
    Next G
    For Each G In ObjDic2
        If (ObjDic1.exists(G)) Then
            ObjDic2.Remove (G)
        End If
    Next G
                          
        Sheets("Structure_XML_Comparison").Cells(LastRow, 1) = "Unchanged Items"
        Sheets("Structure_XML_Comparison").Cells(LastRow, 2).Resize(ObjDic11.Count, 5) = Application.Transpose(Application.Transpose(ObjDic11.Items))
        Sheets("Structure_XML_Comparison").Cells(LastRow, 8).Resize(ObjDic11.Count, 5) = Application.Transpose(Application.Transpose(ObjDic11.Items))
        LastRow = LastRow + ObjDic11.Count + 1
    
'-----  MODIFIED  ITEMS
        Sheets("Structure_XML_Comparison").Cells(LastRow, 1) = "Modified Items"
        Sheets("Structure_XML_Comparison").Cells(LastRow, 2).Resize(ObjDic2.Count, 5) = Application.Transpose(Application.Transpose(ObjDic2.Items))
        Sheets("Structure_XML_Comparison").Cells(LastRow, 8).Resize(ObjDic2.Count, 5) = Application.Transpose(Application.Transpose(ObjDic2.Items))

End Sub

[kalyanr12]

Thanks PCI for the reply. It partially helped me with the comparison. Last time, I attached a document containing concised version of my excel comparison. I am attaching my actual data now:

If you look at the excel document, "Name" is the unique field in both the worksheets, but the no. of columns are not the same (Structure_DATA has 15, while XML_DATA has 12)

Also, in Structure_DATA, the values for "Name" column starts from A6, while the other sheet has it from C5. That needs to be considered.

Secondly, when we dump out the "Added Parameters" in the comparison sheet, we have to dump the entire row (all 12 columns) of the newly added ones from XML_DATA to the Right hand side (As we know that Structure_DATA has 15 columns, may be we can leave one column blank after that and dump the added row)

Similarly for removed parameters, we got to dump the removed parameters from the "Structure_DATA" to the left hand side. We got to dump the entire row (all 15 columns)

Lastly, since the column count between the 2 sheets is not the same, for identifying the modified or unchanged parameters, we just need to compare only the data from the 12 columns of XML_DATA with the same set of columns in Structure_DATA. The Removed, Modified and Unchanged have to be reported on the left hand side (representing Structure_DATA section).


i think your code was pretty close to the requirement. If you could help me with the above way of dumping the data and formatting it in the comparison sheet, that would be of great help...

[kalyanr12]

PCI, Any quick help on this is highly appreciated.

[PCI]

The data layout changed completely, can we say we have to compare
sheet "Structure_DATA column "A" to sheet "XML_DATA" column "C"
then
sheet "Structure_DATA column "A","C","D","E","H","I","J","K","L","M","N","O
to sheet "XML_DATA" column "C","D","E","F","G","H","I","J","K","L","M","N"

[kalyanr12]

Yes PCI. First document that I shared was the first cut I received from our customer. The latest doc has a slightly modified layout and yes your understanding is right. Comparison should be Column A from Structure_DATA and Column C from XML_DATA. This is to find Add or removed items. The second comparison to figure out modified or unchanged ones is exactly as you said. Columns A, C, D, E, H, I, J, K, L, M, N, O from Structure_DATA needs to be compared with Columns C, D, E, F, G, H, I, J, K, L, M and N from XML_DATA.

Sorry for the incovenience caused. This is the final requirement from our customer. Thanks in advance.