Find duplicate values in two worksheets
How to find and remove duplicates from 2 Excel spreadsheets
On this page you will find step-by-step instructions on how to search for duplicates in two Excel lists. Use the Duplicate Remover Wizard to find unique records or remove duplicate values between 2 worksheets.
- Open an Excel workbook with the worksheets you want to search for duplicates.
- Then open Duplicate Remover Advanced Wizard by clicking on its icon
When working with the add-in, on the left you can see the step you are on.
- You can compare values between 2 Excel spreadsheets to find and remove duplicates.
To do this, select the Compare two tables (lists, ranges) mode and choose the range with your main Excel worksheet, in which you want to delete dupes, in the Select Table 1(your first Table) field.
Before you start Duplicate Remover Advanced Wizard, select any cell in your list. The wizard will automatically select the whole Excel table.
On this step you will see all the open workbooks and worksheets in the Select Table 2 (your Second table) field.
- Select the Excel workbook and worksheet you want to de-dupe and you will see it highlighted.
Select the type of data that you are looking for:
- duplicate values (entries that are present in both Excel lists), or
- unique values (entries that are present only in the first Excel table).
On the Select matching columns step you will see a table with 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.
- 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 between worksheets to find duplicates.
- On the Select matching columns step you can also indicate if there are header rows in your Excel sheets.
- It is also possible to choose to ignore extra spaces in matching columns.
- If you may have empty cells in your tables, you can tick the Ignore empty cells option and exclude such cells while searching for duplicates in two Excel worksheets.
Choose what you want to do with the found duplicate rows or unique values.
- If you choose to Select found values, the rows with the found Excel dupes or uniques will be selected in your main spreadsheet.
- 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.
- Add a status column to your main table to see if a row contains duplicates or uniques.
- Copy or move the duplicated results to another location (a new Excel workbook, new/existing worksheet).
- Or delete duplicate values. All found dupes will be removed in seconds :)
Click on the Finish button
Wait for a couple of seconds and let Duplicate Remover to find all duplicates in your Excel sheets.
Enjoy the results.
- ABOUT US