Compare Sheets and find missing Rows

Hi Team,

I need to compare two sheets on same workbook and find missing rows on which sheet and need to write in log (example : text file).

Please advice how to do it?

Thanks
Anand R

Sorry to add one info..


if row count is not matches in any of the sheet then i need to write a log in text file on which sheet the rows are missing

Please advice how to do it?

Thanks
Anand R

hi team,

Any luck?

Thanks
Anand R

Hi Team,

I have attached the excel document in this reply.

Thanks
Anand R

Maybe:

Sub anandrajagopal()
Dim rcell As Range
Dim x As Long
Dim y As Long
Sheets("Compared").Rows(1).Value = Sheets("TD").Rows(1).Value
Sheets("Compared").Rows(1).Font.Bold = True
x = Sheets("TD").Range("A" & Rows.Count).End(3).Row
y = Sheets("DB2").Range("A" & Rows.Count).End(3).Row
If y > x Then
With Sheets("TD")
    For Each rcell In .Range("A2:H" & y)
        If rcell.Value <> Sheets("DB2").Cells(rcell.Row, rcell.Column) Then
            Sheets("Compared").Cells(rcell.Row, rcell.Column) = "FALSE"
            Sheets("Compared").Cells(rcell.Row, rcell.Column).Interior.ColorIndex = 3
        Else
            Sheets("Compared").Cells(rcell.Row, rcell.Column) = "TRUE"
        End If
    Next rcell
End With
End If
If x > y Then
With Sheets("DB2")
    For Each rcell In .Range("A2:H" & x)
        If rcell.Value <> Sheets("TB").Cells(rcell.Row, rcell.Column) Then
            Sheets("Compared").Cells(rcell.Row, rcell.Column) = "FALSE"
            Sheets("Compared").Cells(rcell.Row, rcell.Column).Interior.ColorIndex = 3
        Else
            Sheets("Compared").Cells(rcell.Row, rcell.Column) = "TRUE"
        End If
    Next rcell
End With
End If
End Sub

Hi,

Thanks for you code.. it is working fine...

But my requirement is that i need to get the missed rows and log into text files and saying that DB2 or Teradata rows are missing.

Thanks
Anand R

Sorry I'm a little confused. Can you post a sample of what your Compare sheet should actually look like when the macro is run? Or am I still not getting it?

Hi John

PFA my sample document. In our scenerio, we have dynamic columns in each workbook and not specified to fixed number of columns. So we need dynamic used range columns to validate.

If sheets DB2 and TD will always have the same amount of columns then maybe this will work for you?

Sub anandrajagopal()
Dim rcell As Range
Dim x As Long
Dim y As Long
Dim z As Long
Sheets("Compared").Rows(1).Value = Sheets("TD").Rows(1).Value
Sheets("Compared").Rows(1).Font.Bold = True
z = Sheets("TD").Range("A1").End(xlToRight).Column
x = Sheets("TD").Range("A" & Rows.Count).End(3).Row
y = Sheets("DB2").Range("A" & Rows.Count).End(3).Row
If y > x Then
With Sheets("TD")
    For Each rcell In .Range(.Cells(2, 1), .Cells(y, z))
        If rcell.Value <> Sheets("DB2").Cells(rcell.Row, rcell.Column) Then
            Sheets("Compared").Cells(rcell.Row, rcell.Column) = "FALSE"
            Sheets("Compared").Cells(rcell.Row, rcell.Column).Interior.ColorIndex = 3
        Else
            Sheets("Compared").Cells(rcell.Row, rcell.Column) = "TRUE"
        End If
    Next rcell
End With
End If
If x > y Then
With Sheets("DB2")
    For Each rcell In .Range(.Cells(2, 1), .Cells(x, z))
        If rcell.Value <> Sheets("TB").Cells(rcell.Row, rcell.Column) Then
            Sheets("Compared").Cells(rcell.Row, rcell.Column) = "FALSE"
            Sheets("Compared").Cells(rcell.Row, rcell.Column).Interior.ColorIndex = 3
        Else
            Sheets("Compared").Cells(rcell.Row, rcell.Column) = "TRUE"
        End If
    Next rcell
End With
End If
End Sub