Detecting Duplicate Entries

kkallay · Mar 15th, 2002, 01:13 PM

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 · Mar 15th, 2002, 01:15 PM

This may be of interest:

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

Al Chara · Mar 15th, 2002, 01:21 PM

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 · Mar 15th, 2002, 01:32 PM

Quote:

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 · Mar 15th, 2002, 01:36 PM

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

Mar 15th, 2002, 01:15 PM	#2
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	This may be of interest: http://www.cpearson.com/excel/duplicat.htm __________________ Regards, Nate Oliver Microsoft Excel MVP Nate's Excel Blog

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 15th, 2002, 01:13 PM	#1
kkallay New Member Join Date: Mar 2002 Posts: 1	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

Mar 15th, 2002, 01:21 PM	#3
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	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.

Mar 15th, 2002, 01:36 PM	#5
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	=countif($1$100,a1)>1