Wednesday, April 18, 2012

Highlight Repetitive Data

Building on the theme of Conditional Formatting that was discussed last week, there are several instances where having Repetitive Data (identical data entered more than once) into a database is Undesirable. For instance, if you were an HR Manager, you may wish to have the names of employees entered Only Once in a company database.

It is useful, therefore, to have a Tool to Identify any such repetitive entries. Using Conditional Formatting with a Clever Formula is one way to accomplish this. Noticing that we are using the above Excel graphic as an example, the following COUNTIF formula, (please note the less-than and greater-than characters that together mean Not Equal To in Excel), can do the trick:

=COUNTIF($A$1:$C$5, A1)< >1

Notice too that the first argument refers to the entire database, and uses an Absolute Reference, whereas the second argument refers to the first cell in the database, and uses a Relative Reference.

Since you are using this formula in a Conditional Formatting statement, you want to have a result of TRUE whenever you want the infringing cell to be highlighted. When the formula returns a TRUE, it activates your chosen formatting.

Clever, eh? Give it a try!

No comments: