Comparing two sheets and adding a comment

[Jordan12]

Hi Guys

I hope that somebody can help please. I am trying to create a macro that can compare data between 2 sheets I found this macro below and it works nicely. However I am hoping to modify it and I need help. As below it checks the data in column A, B, C against sheet2 column A, B, C ect. If it does not match the data is written to "unmatched" sheet. What I would to do is add a comment in column J that says which column did not match.

For i = 2 To LastRowSrc
For J = 2 To LastRowData
Found = False


Temp1 = Src1SH.Cells(i, "A") & Src1SH.Cells(i, "B") & Src1SH.Cells(i, "C") & Src1SH.Cells(i, "D") & Src1SH.Cells(i, "F")
Temp2 = Data1SH.Cells(J, "A") & Data1SH.Cells(J, "B") & Data1SH.Cells(J, "C") & Data1SH.Cells(J, "D") & Data1SH.Cells(J, "F")

If (Temp1 = Temp2) Then
Src1SH.Range(Src1SH.Cells(i, "A"), Src1SH.Cells(i, "H")).Interior.ColorIndex = 4
Data1SH.Range(Data1SH.Cells(J, "A"), Data1SH.Cells(J, "H")).Interior.ColorIndex = 4
Found = True
Exit For
End If
Next J


If (Not (Found)) Then
LastRowStore = LastRowStore + 1
Src1SH.Range(Src1SH.Cells(i, "A"), Src1SH.Cells(i, "I")).Copy Destination:=StoreSH.Cells(LastRowStore, "A")

End If
Next I

Regards
Jordan

 

[tonyyy]

Jordan,

...add a comment in column J that says which column did not match.

The above is a little ambiguous, as I don't think you really mean the equivalent of right-click Insert Comment, but simply add the unmatched column(s) letter into Column J.

[COLOR=#0000ff]Dim col As String[/COLOR]
For I = 2 To LastRowSrc
    For J = 2 To LastRowData
        Found = False
        Temp1 = Src1SH.Cells(I, "A") & Src1SH.Cells(I, "B") & Src1SH.Cells(I, "C") & Src1SH.Cells(I, "D") & Src1SH.Cells(I, "F")
        Temp2 = Data1SH.Cells(J, "A") & Data1SH.Cells(J, "B") & Data1SH.Cells(J, "C") & Data1SH.Cells(J, "D") & Data1SH.Cells(J, "F")

        If (Temp1 = Temp2) Then
            Src1SH.Range(Src1SH.Cells(I, "A"), Src1SH.Cells(I, "H")).Interior.ColorIndex = 4
            Data1SH.Range(Data1SH.Cells(J, "A"), Data1SH.Cells(J, "H")).Interior.ColorIndex = 4
            Found = True
            Exit For
        End If
    Next J

    If (Not (Found)) Then
        LastRowStore = LastRowStore + 1
        Src1SH.Range(Src1SH.Cells(I, "A"), Src1SH.Cells(I, "I")).Copy Destination:=StoreSH.Cells(LastRowStore, "A")
        [COLOR=#0000ff]col = ""
        If Src1SH.Cells(I, "A") <> Data1SH.Cells(J, "A") Then col = "A"
        If Src1SH.Cells(I, "B") <> Data1SH.Cells(J, "B") Then col = col & "B"
        If Src1SH.Cells(I, "C") <> Data1SH.Cells(J, "C") Then col = col & "C"
        If Src1SH.Cells(I, "D") <> Data1SH.Cells(J, "D") Then col = col & "D"
        StoreSH.Cells(LastRowStore, "J").Value = col[/COLOR]
    End If
Next I

Please note the code is untested.

Cheers,

tonyyy

 

[Jordan12]

Hi Tony

Thank for the feedback. Great help, I tested it and it works however it does not give me the result I would like. It is putting al h columns into col J. I would it to display which col is incorrect in col J or highlight the affected cell:


If StoreSH.Cells(i, "A") <> Data1SH.Cells(J, "A") Then col = StoreSH.Cells(i, "A").Interior.ColorIndex = 4
If StoreSH.Cells(i, "B") <> Data1SH.Cells(J, "B") Then col = StoreSH.Cells(i, "B").Interior.ColorIndex = 4
If StoreSH.Cells(i, "C") <> Data1SH.Cells(J, "C") Then col = StoreSH.Cells(i, "C").Interior.ColorIndex = 4
If StoreSH.Cells(i, "D") <> Data1SH.Cells(J, "D") Then col = StoreSH.Cells(i, "D").Interior.ColorIndex = 4

Thanks
Jordan

 

[tonyyy]

A question regarding your nested loops...

For i = 2 To LastRowSrc
    For J = 2 To LastRowData

The above will compare row 2 of the Src1SH to every row in the Data1SH, then it will compare row 3 of the Src1SH to every row in the Data1SH... until the last row in the Src1SH. Is that your intent? If so, then it's very likely the macro is producing correct results.

Or did you really mean to compare row 2 to row 2, row 3 to row 3, etc? In which case you need to delete the "For J = 2 To..." loop and simply replace the j's with i's to compare rows of the two sheets. For example...

Temp1 = Src1SH.Cells(I, "A") & Src1SH.Cells(I, "B") & Src1SH.Cells(I, "C") & Src1SH.Cells(I, "D") & Src1SH.Cells(I, "F")
Temp2 = Data1SH.Cells(I, "A") & Data1SH.Cells(I, "B") & Data1SH.Cells(I, "C") & Data1SH.Cells(I, "D") & Data1SH.Cells(I, "F")