Find duplicates in two Excel sheets

Term duplicates is applicable to the tables with database structure. It means records which are the same by specific criteria. You can use different criteria to define which records you treat as "identical".

The most common cases:

• Equal Primary Keys If you have defined Primary Keys (columns and rows) on your worksheets, your sheet became a database table, stored in Excel. Primary key should be unique in the database, because it's purpose it to identify the record. However usually we receive documents where keys are duplicated and records should be processed and combined. Or you just need to remove the duplicated record.
• Completely Equal Rows If you have a list of rows on the worksheet, it may have a copies. Usually copies doesn't have any sense, so typical business case - get read of the duplicated records.

Described cases are applicable to single worksheet - filter database table and remove duplicates and two worksheets - compare records in 2 excel sheets and exclude duplicates.

Imagine you have 2 different worksheets, saved in the Excel Files. In our example this is File1.xlsx and File2.xlsx. In this article we show you how to remove records from File2.xlsx, which are present in File1.xlsx. This is simple step-by-step tutorial which shows how easy this is done by xlCompare. With our product you save your time and increase productivity.

1 Drop your excel files into xlCompare window from the Windows Explorer. xlCompare application opens and displays your worksheets side-by-side. This in form of the User Interface the most suitable for comparison and consolidation operations.

2 Define Primary Keys on the worksheets. Primary keys and important part of the Excel Tables.

3 Press Compare Button on the Home Tab on the Ribbon. Now you get Comparison Wizard. This is a window where you select pairs of the compared and merged worksheets and setup comparison algorithm, applied to worksheets to get the best comparison results.

4 Now you have comparison results and duplicate rows (which are present on both worksheets) are not marked with color. New and deleted rows are marked with green and red background color. According to our task we need to remove unformatted rows from the right panel.

5 Apply Filter command on the Difference Explorer toolbar. In this wizard you select equal rows (filter duplicates only). Now you have duplicate rows on both sides. If you are looking for duplicates by key, possible some values in other fields are changed. In this report you see all changed fields.

6 Select all records on the right panel and use Delete command from the Right Click Menu. It removes all rows from the right panel.

7 Cancel Filter and Stop the Comparison. Now your right worksheet (FILE2 panel) has only new rows which are not present in the left sheet (FILE1)

This list could have less items, but we've tried to include every even minor step.

Another very common example - you have a list of records (price list, part list, application log, etc.). The task is to remove duplicates from this list.

1 Drop yoru Excel Files into the Excel Compare window.

2 Go to Database | Duplicate Keys | Hide command on the Ribbon. This command groups records by the duplicate key on both worksheets and leaves only first record visible in the list. Other duplicate rows are hidden.

3 Use Database | Remove | Hidden Rows command on the Ribbon. Now your worksheet is clear from the duplicate records. Also this step shows how to remove rows you don't need on the sheet. Hide them and delete with this command.

As you see - just in a few clicks you delete duplicate keys and rows from your worksheet. xlCompare does your work in a moment.

See Also

How to integrate Excel Compare into GIT enviroment

How to activate xlCompare after placing an order

Compare 2 excel files by viewing them side-by-side