Find duplicate values in two worksheets

How to find and remove duplicates from 2 Excel spreadsheets

Compare two Excel spreadsheets and remove duplicates

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.

Free Download
Free 15-day trial version, 9/8/2014
  1. Open an Excel workbook with the worksheets you want to search for duplicates.
  2. Then open Duplicate Remover Advanced Wizard by clicking on its icon Advanced Wizard icon

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

See the steps you take on the left

Select the "compare 2 tables" search mode

  1. 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.
    Enter the address of the range with your main table in this field

    Tip

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

Select the second Excel worksheet

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

  1. Select the Excel workbook and worksheet you want to de-dupe and you will see it highlighted.
    Enter the address of the second table in this field

Choose data you want to find: duplicates or unique values

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).
Choose if you want to find duplicate or unique values

Select columns to be compared for duplicate records

On the Select matching columns step you will see a table with a list of columns in both Excel spreadsheets.

  1. 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. Select columns to be compared
  2. If you have a lot of columns in your table, take advantage of the Uncheck All button 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.
  3. On the Select matching columns step you can also indicate if there are header rows in your Excel sheets. See the headers of your tables by selecting these options
  4. It is also possible to choose to ignore extra spaces in matching columns.
    This option lets you disregard leading or trailing spaces
  5. 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.
    Exclude empty cells from the search

Select the action for the found dupes (uniques)

Choose what you want to do with the found duplicate rows or unique values.

  1. If you choose to Select found values, the rows with the found Excel dupes or uniques will be selected in your main spreadsheet.
  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 duplicated results to another location (a new Excel workbook, new/existing worksheet).
  5. Or delete duplicate values. All found dupes will be removed in seconds :)
Choose what to do with the found entries

Click on the Finish button

Wait for a couple of seconds and let Duplicate Remover to find all duplicates in your Excel sheets.

The data are processed in a few seconds

Enjoy the results.

The dialog box with the result
Main table with the results

See also

Featured customers

Ablebits.com featured customers
 
Contact us
 
  Publish this message on the forum
 

Please remember that we work from 8am to 4pm GMT,
this is 4:00am to 12:00pm EST, 1:00am to 9:00am PST, and our public holidays may differ from yours.

Ultimate Suite for Excel Professionals
 
 
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard