• Subcribe to Our RSS Feed

Spot Duplicate Entries

Feb 4, 2007 by     1 Comment    Posted under: Tips and Tricks

Tips and tricksThis is another excel tip I Copied from my files. This excel tips describes a way to use the Conditional Formatting feature to quickly identify dupes (duplicates) in a range. Say that you’ve been saddled with the job of organizing the seating list for the company dinner party. You have the list of assignments in range A1:B18. Obviously, the same person should not be assigned to more than one table. Here’s a quick way to determine if the range contains duplicate names.

1. Select the range (in this case, A1:B18).

2. Choose Format, Conditional Formatting to display the Conditional Formatting dialog box.

3. Select Formula Is from the first drop-down list box, and enter =COUNTIF($A$1:$B$18,A1ยป1 in the second box.

4. Click the Format button to bring up the Format Cells dialog box.

5. Select the Patterns tab, and specify a background color.

6. Click OK twice to return to your worksheet.

7. If the range contains any duplicate entries, they will be flagged with the background color you chose in Step 5.

You can adapt this technique to any other worksheet by changing the ranges used in the COUNTIF function. The first argument should be the full address of the range you selected–and make sure that you use the dollar signs to signify an absolute reference. The second argument should be the address of the upper left cell of your range, expressed as a relative reference (no dollar signs).

Special Thanks to PCWorld as the original source of the above documents.

1 Comment + Add Comment

Got anything to say? Go ahead and leave a comment!

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>