Macro To Compare Two Columns In Excel WorkSheet – Highlight Duplicate Entries

Sometime back we provided the solution for comparing two excel worksheets and highlight the duplicate entries. Our readers were constantly looking for a macro to compare two columns in the same worksheet and highlight the duplicates. Here is a step by step procedure to compare data in two columns of same worksheet in excel and highlight the duplicate entries. This Macro also updates the operation’s progress in status bar and helps user in estimating the time taken for comparison.

Creating Macro

First step is to create a Macro. For creating a macro, click on Tools in menu bar, select Macro and then Macros in sub menu as shown in Figure 1 below.

Figure 1

Clicking on Macros in Figure 1 will open the below window. Type the name of Macro as “Compare” or a name of your choice and click on Create button.

Clicking on Create button will launch the Visual Basic editor as below.

Figure 3

Copy below code and paste it in the editor.

Save this Macro by closing the editor.

Running Macro

Select active sheet (Sheet1 in this example) and run the Macro by clicking on Tools->Macro->Macros. Select the Macro “Compare” and click on “Run” button. Duplicate entries will be highlighted in first column.

In the above Macro, it is assumed that first row is a header row. Also, worksheet names is set as “Sheet1″ (refer variable WS in above macro) and columns to be compared are considered to be “1″ and “2″ (refer variables c1 and c2). To change the worksheet names/column numbers or customize this Macro further, follow below steps.

Editing Macro

  • Go to Tools->Macro->Macros. Select the Macro “Compare” and click on “Edit” button.
  • This will launch the Visual Basic Editor.
  • Customize the code as per the requirements.
  • Save the Macro.

Hope you find this article useful, your suggestions and feedback are always welcome. Thank You.

      • One thing I’d really like to touch upon is that fat reodctiun plan fast may be accomplished by the suitable diet and exercise. Ones size not merely affects the look, but also the overall quality of life. Self-esteem, major depression, health risks, and physical capabilities are damaged in putting on weight. It is possible to make everything right and still gain. In such a circumstance, a problem may be the perpetrator. While too much food but not enough exercise are usually to blame, common health concerns and trusted prescriptions may greatly add to size. Thanks alot : ) for your post in this article.

  1. Hi Team,

    I am in need of some data crunching methodology. My scenario is this,

    I have to test 9 devices each with 3 temperatures(hot, room, cold) so totally 9*7=27 data.
    In one sheet I ll test for some 200 tests.

    test 1 : 1st devices, hot temperature

    test 2 : 1st devices, hot temperature

    test 3 : 1st devices, hot temperature

    test 4 : 1st devices, hot temperature

    test 5 : 1st devices, hot temperature

    test 6 : 1st devices, hot temperature
    test 200 : 1st devices, hot temperature

    test 1 : 2nd devices, hot temperature

    test 2 : 2nd devices, hot temperature

    test 3 : 2nd devices, hot temperature

    test 4 : 2nd devices, hot temperature

    test 5 : 2nd devices, hot temperature

    test 6 : 2nd devices, hot temperature
    test 200 : 2nd devices, hot temperature
    . so on for 27 sheets..

    I want to crunch all data into one sheet such that I should do minimum enteries and maximum output.
    In output sheet I should get max, min, avg values of the data entered.

    For eg. Max(sheet 1 , hot temp, 1st device: sheet2, hot temp, 2nd device…. Upto 27 devices)

    I need a format how to put this data into excel and get these kind of maximum outputs and easy for a reader.

    Thanks in advance. Hoping for your favorable reply.

    Ralins A

  2. Hi Ralins,

    We understand that there is some manual process that you want to automate using Excel.

    We request you to share a sample XLS with all inputs and desired output for us to have a better understanding of the requirement.


    • My wife and i ended up being toyoruq now jofuyl that Raymond could conclude his survey from the ideas he got through the web page. It is now and again perplexing to simply possibly be releasing things that some others may have been making money from. We really take into account we now have you to appreciate for this. All of the illustrations you’ve made, the simple website navigation, the friendships you will assist to promote it’s all fantastic, and it is leading our son in addition to our family understand this article is fun, which is certainly seriously indispensable. Many thanks for all!

  3. Thanks Admin.. I am new to programming and scripting.. I want to learn it more. Your solutions helps me a lot. Thanks Again – Arvind :)

  4. First of all thanks this macro is very useful. I would like to not have it filter on c2 upon completion. But I am having issues trying to figure out how to stop that. Can you help?

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="">