Compare two sheets for rows added, deleted, modified and no data change

**Prathamesh1991** · 05-11-2018, 03:38 AM

Hi Guys,

Can anyone help me with the macro to compare two sheets which will give me below 4 results

New Row added
Row deleted
Row modified
Row is same as previous sheet

I tried recording macro using vlookup and sha1 hash; however it won't give me results based on deleted and newly added rows.

Any help regarding this is greatly appreciated.

Thank you

**JeteMc** · 05-16-2018, 10:24 PM

Hello Prathamesh1991 and Welcome to Excel Forum.
If I understand correctly then perhaps this will help.
Sheets 1 and 2, columns A:D contain raw data.
Column F on those sheets contain concatenated data for each row using: =A1&B1&C1&D1
Column A on sheet 3 compares the values in column F of the two sheets using:

Formula:

=IF(Sheet1!F1="","Added",IF(Sheet2!F1="","Deleted",IF(Sheet1!F1<>Sheet2!F1,"Modified","Same")))

If this doesn't meet your needs I would suggest uploading a desensitized sample of your workbook by clicking on the GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window.
Let us know if you have any questions.

**Prathamesh1991** · 05-28-2018, 01:45 PM

Hello JeteMc,

Thank you so much for your help. This is what exactly I required; however, is it possible to incorporate this in a macro ? Also is it a possible that the current formula will not work if number of columns are high and character length of cell also greater than 250? like Vlookup ?.

Also can you please guide me to write a code to concat all the columns dynamically?

Thank you so much for your help and support . Greatly appreciated.

**JeteMc** · 05-28-2018, 07:38 PM

I am VBA illiterate, so I will not be able to write a macro or code to concatenate all columns dynamically. I will see if someone from the VBA side can work on this, however in the mean time I suggest that you upload a desensitized version of your actual spreadsheets so that they will have something to go on. To upload a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

**CAntosh** · 05-29-2018, 04:26 PM

A sample workbook would help us find a more precise solution, but the procedure below is a pretty straightforward copy of JeteMc's approach:

Sub Sheet1vsSheet2()
Dim i As LongPtr
Dim j As LongPtr
Dim LR As LongPtr
Dim LR1 As LongPtr
Dim LR2 As LongPtr
Dim LC As LongPtr
Dim LC1 As LongPtr
Dim LC2 As LongPtr
Dim Conc1 As String
Dim Conc2 As String

LR1 = Sheets(1).UsedRange.Rows(Sheets(1).UsedRange.Rows.Count).Row
LR2 = Sheets(2).UsedRange.Rows(Sheets(2).UsedRange.Rows.Count).Row
LR = Application.WorksheetFunction.Max(LR1, LR2)
LC1 = Sheets(1).UsedRange.Columns(Sheets(1).UsedRange.Columns.Count).Column
LC1 = Sheets(2).UsedRange.Columns(Sheets(2).UsedRange.Columns.Count).Column
LC = Application.WorksheetFunction.Max(LC1, LC2)
Application.ScreenUpdating = False
Sheets(3).Range("A:A").ClearContents
For i = 1 To LR
    Conc1 = ""
    Conc2 = ""
    For j = 1 To LC
        Conc1 = Conc1 & Sheets(1).Cells(i, j).Value
        Conc2 = Conc2 & Sheets(2).Cells(i, j).Value
    Next j
    If Conc1 = Conc2 Then
        Sheets(3).Cells(i, "A").Value = "Same"
    ElseIf Conc1 = "" Then
        Sheets(3).Cells(i, "A").Value = "Added"
    ElseIf Conc2 = "" Then
        Sheets(3).Cells(i, "A").Value = "Deleted"
    Else
        Sheets(3).Cells(i, "A").Value = "Modified"
    End If
Next i
Application.ScreenUpdating = True
End Sub

It should compare each row on sheet1 to the same row on sheet2 and report the results in column A of sheet 3. It should also dynamically account for any added rows or columns. Experiment a bit with the attachment to see if it's working as desired: