Detecting Duplicate Entries

[kkallay]

I would like to display duplicate entries (which are allowed)in a column in red. I've tried this in conditional formatting (Formula Is)using the EXACT function to compare one cell against a range, but can't get it to work properly. I've applied the following to all cells in the column:
=EXACT(A3,$A$1:A2)
Is what I want to do possible?
Thank You

 

[NateO]

This may be of interest:

http://www.cpearson.com/excel/duplicat.htm

 

[Al Chara]

Conditional formatting sounds good. But your use of EXACT is wrong. Instead of comparing a cell to a formula in conditional formatting, try selecting cells A1 and A2, goto Format|Conditional formatting|Cell value is|equal to|=$A$3
This will compare cells Al and A2 to cell A3.

Hope this helps.
Kind regards, Al.

 

[Aladin Akyurek]

On 2002-03-15 12:13, kkallay wrote:
I would like to display duplicate entries (which are allowed)in a column in red. I've tried this in conditional formatting (Formula Is)using the EXACT function to compare one cell against a range, but can't get it to work properly. I've applied the following to all cells in the column:
=EXACT(A3,$A$1:A2)
Is what I want to do possible?
Thank You

Select the range of interest (say, A1:A100).
Activate Format|Conditional Formatting.
Choose 'Formula Is' for Condition 1.
Enter in the formula box:

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

Activate Format.
Choose red either on Font or Patterns tab.
Click OK, OK.

 

[Brian from Maui]

=countif($1$100,a1)>1