Find and delete duplicates in Excel tables - check for duplicate in Excel 2010 - 2000

Duplicate Remover for Microsoft Excel

Finding and deleting duplicates – a duplicate checker for Excel 2010, 2007 - 2000

For Microsoft Excel 2010 32-bit and 64-bit, Excel 2007, Excel 2003.

On this page you will find step-by-step instructions on how to work with Duplicate Remover for Excel, in particular, how to check 2 Microsoft Excel lists / tables / ranges for duplicates and unique entries.

• Select the search mode
• Select the second table
• Choose the type of data you want to find: duplicate or unique values
• Select the columns to be compared
• Select the action for the found entries

1. Open Excel workbook(s) with the tables you want to compare.
2. Then open Duplicate Remover Advanced Wizard by clicking on its icon
   If you work in Excel 2003 - 2000, you can also go to Excel menu -> Ablebits.com -> Duplicate Remover for Excel -> Advanced Wizard

When working with the add-in, on the left you can see the step you are on.

Select the search mode

1. Select the Compare two tables (lists, ranges) mode and choose the range with your main table in the Select Table 1(your first Table) field. You can select the table by clicking the select range icon or enter its address in the Select Table 1(your first Table) field.
   
   

   Tip: Before you start Duplicate Remover Advanced Wizard, select any cell in your list. The wizard will automatically select the whole table.

2. Click the Next button on the Duplicate Remover dialog box to continue.

Select the second table

On this step you will see all the open workbooks and worksheets in the Select Table 2 (your Second table) field.

1. Select the workbook and the worksheet that contains your second table and you will see it highlighted.
   If it is not highlighted, select the table by clicking on the select range icon next to the Data range field or by manually typing in the address of the range with the table into this field.
   
   

   Tip: You can just select one cell in the table. The wizard will automatically set the range of your second table (list).

2. Then click the Next button on the Duplicate Remover dialog box to continue.
3. If you need to get back to step 1, press Back on the dialog box.

Choose the type of data you want to find: duplicate or unique values

Select the type of data that you are looking for:

• duplicate values (entries that are present in both lists), or
• unique values (entries that are present only in the first table).

Select the columns to be compared

On the Select matching columns step you will see a table with a list of columns.

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

   Tip: Columns with the same names are selected automatically.

   
   

   Note! If you select more than one column, the add-in looks for "row" duplicates. So if you select 2 columns, only those records that have the same values in both columns in two tables will be considered duplicates. E.g. If "Fisher 36 Oak Drive" is duplicated in the same columns in your second table, the add-in will find it.

2. If you have a lot of columns in your table, take advantage of the Uncheck All button and all the columns in the list will be deselected. Then you can tick those you need to compare.
3. On the Select matching columns step you can also indicate if there are header rows in your tables. As a rule, the add-in indicates headers automatically. If it didn't, you can check the Table 1 has headers or Table 2 has headers options correspondingly. This will let you see what kind of information you have in the columns. You can also uncheck this option if you don't want header rows to be indicated.


The 1st row content column between Table 1columns and Table 2 columns shows the 1st item of each column in table1. It can be useful if your table doesn't have headers so you can see what kind of information is in the columns.

4. It is also possible to choose to ignore extra spaces in matching columns. You should tick the Ignore extra spaces in matching columns checkbox if your data may have some extra leading or trailing spaces. This option will prevent the add-in from missing some values.
   
5. If you may have empty cells in your tables, you can tick the Ignore empty cells option and exclude such cells from the search results.
   
6. Click on the Next button on the Duplicate Remover dialog box to proceed.
7. If you need to get back to step 3, press the Back button on the dialog box.

Select the action for the found values

Choose what you want to do with the found values.

1. If you choose to Select found values, the rows with the found entries will be selected in your main table.
2. You can Color values to change the background color of the found rows. You can pick the color from the drop-down list next to the Color values option.
3. Add a status column to your main table to see if a row contains duplicates or uniques.
4. Copy or move the results to another location (a new workbook, new/existing worksheet).
5. Or delete the rows with the found duplicate or unique values.

Click on the Finish button

Wait for a couple of seconds until your data are processed.

Enjoy the results.

See also

• Finding duplicates in a single Excel list

Download now and see how it can help you