How to Compare Two Excel Spreadsheets for Duplicate Rows

1. Open your spreadsheet and make sure you know what columns the data you want compared are in. This example will assume the data is in column A, and that the two lists to be compared are in two spreadsheet tabs, labeled Sheet1 and Sheet2 of the same workbook.
2. Enter the following formula in cell B1 of Sheet1. =COUNTIF(Sheet1A:A,Sheet2A:A). Hit Enter. If the record is unique (it doesn't appear on both lists), this will evaluate to 0.
3. Copy the formula from cell B1 through as many rows as you need on Sheet1.
4. Select columns A and B, and click on the Home tab (in Excel 2007) and select Sort and Filter. Choose to filter the results by Column B in ascending order. This will move all the results with a '0' in column B to the top, putting all your duplicate records at the bottom of the list.