Macro To Compare Two Excel WorkSheets and Highlight Duplicate Rows

We have been providing solutions for automating the job of comparing excel worksheets which otherwise is a tedious job and our readers requests are increasing day by day. Here is one more to that list. One of our reader was looking for a macro to compare two excel worksheets and highlight duplicate rows. Here is the vb code to do this job. This Macro also updates the operation’s progress in status bar for estimating the time taken for comparison.

Note: To find out the detailed steps on how to create, run or edit a macro, you may refer here.

Few Points Worth To Note:

  • While creating the Macro, ensure that name of the macro is kept as “compare”
  • Worksheet names are considered to be “Sheet1″ and “Sheet2″
  • Duplicate rows are highlighted in “Sheet1″

Hope you find this solution useful, your suggestions, feedback and comments are always welcome. Do let us know if you were looking for a different macro.

  1. I have tried the above a number of times and it makes excel freeze and i have to close and open the software up again. I don’t know if the code is too big or if its the files that i am using. please help.
    Thank you

  2. Hi Cali33,

    How big is your data that you are trying to compare?

    Just to let you know, we are comparing each cell of each row of first sheet with each cell of each row of second sheet, until we ensure that it is non-duplicate.

    Regards

  3. I have a similar problem. When I try to compare sheet1 (38,000 records) with sheet 2 (2400 records), I get Excel Argument not optional (Error 449). I would appreciate any help. Thank you.

  4. This seems to be close to what I am looking for; however I was wondering if there is a way to compare two workbooks instead of just sheets? I am starting a new job where I compare workbooks and have heard there is a macro code for that as well. Thank you!

    • Hello again,

      What I need to know is can two workbooks be compared for duplicates at the same time?
      We work with thousands of entries and hundreds of workbooks that sometimes have duplicates that needs to be marked.

      Thanks

  5. Hi,
    I have tried the macro for highlighting duplicates and it works, it’s a brilliant bit of code. The only thing is if sheet 1 has information from a week ago and sheet 2 has new information it won’t highlight the relevant information that I need to look at in sheet2. I am always looking at sheet 2 because it has the new update changes. Is there any way that the formula can be changed so that sheet 2 shows the duplicates? If so which bits of the code do I need to change?
    Thank you,

  6. Hi Richard,

    You may try swapping the sheet names in below line in above code:

    Change this line:

    CompareWorksheets Worksheets(“Sheet1″), Worksheets(“Sheet2″)

    With

    CompareWorksheets Worksheets(“Sheet2″), Worksheets(“Sheet1″)

    Now, please select Sheet2 and run the macro. This should work for you.

    Feel free to let us know if you still face any issues.

    Regards

  7. Hi,
    Is there anyway that the marco can look at one column instead of comparing everything in the document? Im look at column C sheet 1 to compare column C in sheet 2. Is this doable?
    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">