Find duplicates and unique rows in two sheets in Excel
Find duplicate rows between Excel sheets using formulas
If you just need to identify different and matching rows on two Excel sheets, you can use Excel for this. Excel itself is a powerful application. It is the best spreadsheet application.
In this tutorial we will use simple Excel file with SKU units. It contains two worksheets where:
- column A represents SKU CODE
- column B - MSRP PRICE
- column C contains RETAIL PRICE
Our goal is to find rows between Sheet1 and Sheet2, which have the same SKU codes.
Sheet1 is a source worksheet, Sheet2 is a sheet where we are going to find the matching rows.
Use MATCH function to find the matching value in the source worksheet
Select the first data cell after the rightmost column on Sheet2. In our example this is cell D2. Columns A:C contains data, so we should use column D.
Our sheet contains one heading row, so we skip the heading rows, and select the second row.
If the data, you are comparing doesn't have a heading - just use the first data row (D1).
Enter following formula into cell D2:
=IF(ISNA(MATCH(A2,Sheet1!A:A,0)),"","MATCH")
It returns MATCH word in our example. This means that this SKU is present on the other worksheet. In case if this value is not present - formula returns empty value.
Expand formula to the whole table
This is very easy. Just double click on the fill handle and Excel will expand this formula to the whole table:
As you see we have several matching values. Now we can start working with results.
Format matching rows with color
We use Conditional Formatting for this. You don't need to learn the conditional formatting feature. We will use a small part of its capabilities. Just repeat several steps we've described below.
Select Cells
Select entire worksheet Sheet2 by clicking on the left-top corner of the window.
Add New Rule
Use Conditional Formatting | New Rule command to add new conditional formatting rule to your worksheet.
Enter Formula
Select Use Formula to determine which cells to format option.
Enter following formula
=$D1="MATCH"
It will format the row that contains the MATCH string in the column D. That's why it is important to put dollar sign before D and omit it before 1.
Select Formatting Options
In our example we are using a light green background for the matching rows.
You can select another appropriate formatting you like.
Get the Results
After you've added the conditional formatting rule - you get matching rows on your worksheet formatted with background color.
This looks like a presentation, so you can save this file on disk with formatting.
Filter matching rows on the worksheet
This is also relatively easy to do. Let's use AutoFilter option.
Select resulting column
This is the column that contains MATCH word. In our example this is column D. Click on the heading and get your column selected.
Create Filter
Use menu Sort and Filter | Filter on the ribbon. This command creates a filter in column D.
Filter Matching Rows
Click on the filter button on column D and uncheck the blanks item in the Filter window. Click OK to close the Filter Window.
Now you have only matching rows on your screen.
Filter Unique Rows
To filter unique rows (that are not present on Sheet1) uncheck the MATCH item on the Filter window and check the blanks.
That's all. You have only unique rows in your table.
Delete Unique Rows on two sheets
After you've made previous step - select all visible rows on your worksheet and delete them with a right click Delete Row command.
You've compared two worksheets and removed unique rows!
Restore all other rows by checking all items in the Filter window.
Now your table contains only matching rows.
Conclusion
As you see Excel is a powerful application that is the best in the spreadsheet business. By using several simple steps, you can filter your table from duplicate or mismatching records.
Features we've used:
- MATCH, ISNA, IF worksheet functions
- Conditional Formatting
- Auto Filter
Use xlCompare tool to find matching rows between two sheets
Compare two Excel sheets with xlCompare
Look into the following tutorial. You need to take just a few steps to the results.
Start xlCompare application
Usually, xlCompare shortcut is present on your desktop. So, you can start it from your desktop.
Drop your Excel file into xlCompare
The simplest method to open a file in xlCompare is to drop the file from Windows Explorer right into xlCompare.
So, just drop your file into the Left Panel in xlCompare and into the Right Panel in xlCompare.
Now you have Sheet1 on the left and Sheet2 on the right.
Compare Excel Sheets
Click the Compare sheets button on the Compare tab on the ribbon.
xlCompare immediately gives you comparison results.
Unique rows on both worksheets are highlighted with fill color. xlCompare uses red color on the right sheet and green color on the left sheet.
Also, xlCompare compares values in all matching rows. This is a good benefit you are getting.
Filter Matching Rows in two Excel sheets
xlCompare contains several filtering commands on the ribbon, you can use to filter matching and duplicate values on the sheets.
Apply Filter Duplicates command to get only matching rows on your screen.
Save Matching Rows on the other Excel sheet
After you've completed the previous step, use Save | Visible Rows command from the Right Click Menu. It will ask you to select Excel file for the resulting data.
Get rid of unique rows on two sheets in Excel
Use the Filter Unique Rows button on the xlCompare ribbon.
Now you have only unique rows on your screen. They are highlighted with colors.
Select all rows on the worksheet by clicking on the heading and delete them using the Delete command in the right click menu.
Just in a few clicks you've removed all unique rows between two Excel sheets.
Conclusion
It this tutorial we've explained you how to compare two Excel sheets for matching(duplicate) rows. If your task is simple and you need to filter matching rows - Excel itself is the best for this. Just enter formulas using the instructions above and get the results.
If your worksheet is large and you need to compare values in the matching rows or create a presentation report - xlCompare will do this work for you.
What is the difference between using Excel and xlCompare for comparing Excel tables?
Why should I use xlCompare tool if I can do my job in Excel application.
No formulas, No Conditional Formatting, No Filters
xlCompare transforms the comparison process into several simple clicks. You should not learn anything. You are getting the results without extra effort. This is the reason why you can consider using xlCompare.
xlCompare gives you more options to filter data and save it on disk. It creates presentation reports on the worksheet and in HTML. You can use it to present changes to your colleagues.
See Also
I have a question
Please, describe your question or inquery in the form below. We will be happy to respond you!