Tuesday, August 26, 2008

Identify Duplicates and Uniques in a Spreadsheet

I've been asked by a few people in the last few weeks how to compare the values of two spreadsheets in Excel or Open Office Spreadsheet. I'm not sure if this works for the MAC spreadsheet software as I haven't used it in a long time but assume it should. Each time this question has come from people that are non-technical, in corporate environments that would not allow for a SQL query or macro type solution. The easiest answer given those circumstances that I know of is the following:

1. Copy the contents of list A and add them to the bottom of list B.

2. Paste the following formula in the first cell of the row next to your last row containing data. =IF(COUNTIF($A$1:A1,A1)>1,"Duplicate","Unique")

3. Change the letter 'A' to the letter of the column containing your unique identifier.

4. Place your mouse over the bottom right corner of this cell and drag the formula to all of remaining cells in the column that are adjacent to list item.

5. Sort by the column you pasted the formula in.

Now you have a list of the unique and duplicate Items. If you want to know which items belong to which list then highlight the rows from one of the lists before you sort the data.

Technorati Tags: , , ,