How to find duplicates in two Excel worksheets

The Compare Tables add-in compares two Excel lists for duplicate and unique rows.

Duplicate rows are the rows that are present in both the first and the second table. Unique rows are the rows that are present in table 1 but are missing in table 2. The add-in searches for duplicates and uniques by the key columns you select. The dupes and uniques are identified only in table 1.

Video: How to find duplicates and uniques in Excel

Before you start

Excel instances

Please make sure both tables are opened in Excel. They can be in different files but the add-in will compare them as long as they are open in the same instance of Excel.

Backup copies

We strongly recommend keeping the checkbox Create a backup copy of the worksheet selected, as Excel won't let you undo the changes made by the add-in.

How to use Compare Tables

Start Compare Tables

On the Ablebits Data tab, in the Merge group, click Compare > Compare Tables:
Click on icon to start Compare Two Tables Wizard.

Step 1. Select table 1

Once you start the add-in, you will see the step you are in at the top of the window.

First, select the range with your table 1: all changes will be applied to the table you select in this step.
Select the first Excel table.

If you select just one cell in the table, the add-in will automatically highlight the used range at the start. You can change the range by editing it manually in the Select your first table field, using the Select range icon there, or simply by choosing the right cells in Excel.
Select the range to find uniques or duplicates.

Step 2. Pick table 2

In this step, you can see all open workbooks and worksheets in the Select your second table field. Choose the second Excel worksheet to check for the same values there:
Choose your second table.

Note. If your second table is in another workbook, make sure to open it before starting the add-in.

Click any cell in a table, and get the entire used range selected automatically. If you need to use only a specific range for comparison, click the Select range icon and highlight the necessary cells.

Tip. If you realize that something was chosen incorrectly, you can always get back to the previous step by clicking the Back button.

When the second table is chosen, click Next.

Step 3. Choose the type of data you are searching for

Specify whether you are going to search for duplicate or unique values:
Select to find duplicate or unique rows.

  • Select Duplicate values to find the entries that are present in both table 1 and table 2.
  • Choose Unique values to mark the rows that are present in table 1 but are missing in table 2.

Click Next.

Step 4. Select the columns for comparison

This step displays a list of columns in both Excel spreadsheets.

Select the column(s) from the first table and the matching column(s) from the second worksheet to compare them and check for duplicate or unique data.
Pick the columns to compare.

  1. You can indicate if you have header rows with the help of Table 1 has headers and Table 2 has headers options respectively. You can also check the 1st row content to make sure you match the right records.
  2. If you have empty cells in your tables, tick the Skip empty cells option to ignore them while searching for duplicates and uniques in two Excel worksheets.
  3. Take advantage of the Auto Select button to quickly select all columns with matching headers. Click Unselect All to remove the selection from all columns.

Once the columns are chosen, click the Next button.

Step 5. Specify the desired action

Finally, you can choose what to do with the found duplicate or unique rows:
Choose what to do with dupes or uniques.

  • You can remove duplicate or unique values from the first table by ticking Delete values.
  • To change the background color of the found rows, check Highlight with color and pick the needed hue from the drop-down list.
  • Tick Select values, and the rows with the found Excel dupes or uniques will be highlighted in your first spreadsheet.
  • Select the Identify in the Status column option to insert a special column into your main table and mark duplicates or uniques there:
    Identify duplicates in a status column.
  • You can also copy or move the found results to another location: a new Excel workbook, new or existing worksheet. For this, tick the required option and specify where you want to place the result.

Click Finish and voila! The dupes or uniques are found in your table 1 and processed the way you need.
Compare two Excel sheets and find duplicates and unique rows.

Scenarios

How to save scenarios

In Step 5, before clicking the Finish button, click Save scenario:
Save your scenario.

Decide on a name for your scenario and enter it into the Compare Tables dialog. Then click OK:
Name your scenario.

How to run scenarios

To run any of your saved scenarios, start the Compare Tables tool, select the necessary scenario from the list, specify which worksheet is going to be used as the first table, and click Start:
Select a scenario.

Note. For a saved scenario to work for the current worksheet, the structure of your current table must be the same as that of the table in the scenario.

Responses

Thelma Chiromba says:
April 12, 2023 at 9:19 am

Thank you for the data shared. However l have data of shop that make transactions that are also received by the bank. in this two diffirent sheets DATE, TERMINAL ID, CARD #,REF #, and VALUE are similar. How do l match this data showing me that the very same transcation made at the shop is the same that was seen at the bank.

Hello Thelma,

For us to be able to help you better, please send us a small sample workbook with your source data and the result you expect to get to support@ablebits.com. I kindly ask you to shorten the tables to 10-20 rows.
Note! The result sheet is of great importance and often gives us a better understanding of your task rather than any text description. Please don't forget to include it.
We'll look into your task and see if our software can help.

Hello

I have question about duplicate data.
I want to check any duplicated data between group of sheets.
What can I refer for this problem?
I have 10K data and 10 sheets, I want to check all this data not duplicate in any sheets.

Peter Weaver says:
May 27, 2021 at 2:21 pm

Hi

I see with the newer version it does not remember which sheet/book you compared to last and have to re-select every time. Is it possible to set this as the previous version to remember last compared.

Example I have Sheet 1, Sheet 2, Sheet 3, Sheet 4. I compare Sheet 4 to Sheet 3. If I was to compare Sheet 2 it must automatically reselect Sheet 3 for my next compare. Currently soon as you open compare and decide to select a sheet it goes to the first book opened to first sheet, which in the example will be Sheet 1.

Please advise if it is possible to set it as previous versions such as 16

Hi,
Could you let me know how to compare multiple sheets? I just see "Compare Two Tables", but for our research, we have to compare four sheets from different date.
Thanks,
Charlie

Hi, I have one spreadsheet with a list of names, account numbers and addresses for a contact list and a second spreadsheet with names and account numbers that is a do not call list. I need to compare the two sheets, looking for account numbers, and delete the entire row from the main contact worksheet if the same account number exists on the do not call sheet. I have looked through the tools from Ablebits and don't see any way to be able to do this. Can this be done with your tools, or is there another way to do this that you know of? Any assistance would be appreciated.

Hi Ray,
Thank you for the comment.
Have you tried the Compare Sheets tool?
https://www.ablebits.com/docs/excel-compare-worksheets/

Looks like it does what you need.
Please feel free to contact us if you need any further assistance.

Irina,
I have tried the both the compare two sheets and compare multiple sheets tools, but I don't see an option to show matches, only differences.

Ray,
I have looked into your task once again, and if I understand it correctly, the Compare Tables - the tool of this page - can help you.
Your steps should be the following:
Step 1. Select your main table (the contact list with names, account numbers, and addresses).
Step 2. Select the entire range of your "do not call list".
Step 3. Choose the Duplicate values option.
Step 4. Select the columns with account numbers in table 1 and table 2.
Step 5. Choose the Delete values option.

Please let me know if this helped.

Thank you.

I have a question about a voter 'name matching' problem. *

BTW, news about the 'name matching' problem went 'viral' in Nov 2018 when the man who won for governor of Georgia was also the Secretary of State who wrote the rules for deciding who got to vote. He narrowly defeated Stacey Abrams, who had a chance to become the first black female governor. Even in defeat, she became nationally famous for her fight against the disenfranchisement of a large block of voters whose votes weren't counted because the name was not precisely identical to the registered name and there was no chance to appeal.

* The 'name matching' problem leads me to ask: Do you have plans to enhance the table comparison Action options to = ('equals') with familiar options elsewhere in Microsoft Office such as 'contains'? Right now, when you search large databases of voters Gwen Clifton doesn't match Gwendolyn Clifton.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.