Excel Tip: Deleting Duplicate Entries

Thursday

07Jan2010

Thursday, January 7, 2010 at 7:15PM

Happy New Year fellow entrepreneurs!

The very first Quick Tip of 2010 will teach you to how find duplicate entries in an Excel worksheet. I've worked with worksheets that were extracted from someone's contact list with some contacts listed multiple times.

In your worksheet, insert a blank column (for this purpose the blank column is B). This is where you will enter the formula that will find the duplicate entries for you.

Assuming that row A1 is a column heading, enter the following formula in row B1

=IF(COUNTIF(A$1:A$100,A1)>1,”DUPLICATED”,””)

Now let’s breakdown the formula:

>=IF(COUNTIF: this says to count how many cells in the given range have the same value as A2

>(A$1:A$100,A1): this indicates the range of the data you’re searching for dups

>>1,”DUPLICATED”,””): this says that where do duplicate exists, the count is 1 and the cell will be blank. When a duplicate value is found, the word “DUPLICATED” is entered in the cell.

Now try it for yourself. Please share any tips you have for this Quick Tip or post any questions you might have.

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

Post a New Comment

Enter your information below to add a new comment.

My response is on my own website »

Author:

Author Email (optional):

Author URL (optional):

Post:

↓ | ↑

All HTML will be escaped. Hyperlinks will be created for URLs automatically.