How to compare duplicates in excel

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.

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

Click the Compare Tables icon in the Dedupe group on the Ablebits Data tab:

Step 1. Select table 1

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

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

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.

Step 2. Pick table 2

On 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:

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

Here you are to indicate what you are looking for in table 1:

• 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.

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 how to deal with the found duplicate or unique rows. The add-in offers plenty of options:

• 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.
• If you choose to Select values, the rows with the found Excel dupes or uniques will be simply 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:
  
  
• 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 select the location of the result.

Click Finish and voila! The dupes or uniques are found in your table 1 and processed the way you need!