Excel is used by many businesses as a lightweight database and contacts manager; one of the things this sort of business task requires is comparing lists of contacts and finding duplicate records. Fortunately, Excel's COUNTIF function makes this easy, even when comparing two different spreadsheets in the same workbook.
- Skill level:
Other People Are Reading
Things you need
- Excel 2007 or Excel 2010
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, labelled Sheet 1 and Sheet 2 of the same workbook.
Enter the following formula in cell B1 of Sheet 1. =COUNTIF(Sheet1A:A,Sheet2A:A). Hit Enter. If the record is unique (it doesn't appear on both lists), this will evaluate to 0.
Copy the formula from cell B1 through as many rows as you need on Sheet 1.
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.
Tips and warnings
- This technique can be extended to check for duplicates in other workbooks, by making the COUNTIF function refer to sheets in an external workbook. In general, you are far better off copying the contents of those other workbooks into separate spreadsheets in the same workbook and using the technique here, as linking to external workbooks can result in errors if one of those linked workbooks gets moved, or has its name changed.
- The modified COUNTIF formula would look like this, if Sheet 2 were in a workbook called DataTwo.xls:
- Large numbers of COUNTIF functions can slow down Excel calculations considerably; once you've sorted out your unique records, delete the column with the COUNTIF results before working with the data further.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for