Best Microsoft Excel Bloggers

Monday, February 8, 2010

Duplicates

Great game although The WHO vocals and the ads were a bit of a disappointment.

DUPLICATE VALUES

I've had a number of emails and conversations lately about identifying duplicate values.
Using Conditional Formatting in Excel 2007 makes it relatively easy but not everyone has Excel 2007.
Let's start with Excel 2003:

Let's say that you have a column of numbers in B4:B16 and you want to identify duplicates.

Method 1:
Sort - Easy but sometimes you can't or don't want to manipulate your data.

Method 2: Click in cell C4 and type
and then copy it down.




You will have a column of TRUE and FALSE.  You can manually delete them or highlight them. The problem with this method is that Excel labels both numbers are TRUE - there is no differentiation between the original and the duplicate.

Method 3:  A Better Alternative
To get around this, try =IF(ISNUMBER(MATCH(B4,$B$3:B$3,0)),"Duplicate","Unique").
This will label the original as Unique but any other instances will be labeled as a Duplicate.












Method 4: This method is actually available in Excel 2003 and Excel 2007.
You can use the Advanced Filter function under the Data menu or tab and select Unique Only.
To be honest, I have mixed results with this so double-check your answer.

Select the column of numbers and then go to the Data tab or menu and select Advanced Filter.
Click copy to another location
Enter a cell to Copy To:
Put a checkmark in Unique Records Only
Click OK.




Well, it's getting late. Tomorrow I'll talk about how to identify Duplicates in Excel 2007.

No comments:

Post a Comment


Ms. Excel- Resident Excel Geek