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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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: