Finding duplicate cell values

[Egreerc]

How do I find duplicate values in my spreadsheet? Example: 10001 in Cell A:2 and 10001 in Cell A:25.

[Ricky Morris]

On 2002-04-26 08:53, Egreerc wrote:
How do I find duplicate values in my spreadsheet? Example: 10001 in Cell A:2 and 10001 in Cell A:25.

Try the following array formula:

=IF(A1=$A$1:$A$100,"Duplicate",""). Copy to an adjacent colum from rows 1 to 100. Duplicate values will display "Duplicate"

Array formulas should be entered by pressing Ctrl-Shift-Enter at once. When entered correctly Excel will put {} around the formula to identify it as an array.

[Mark W.]

Enter the formula...

=IF(COUNTIF($A$2:$A$25,A2)>1,"Duplicate","")

...into B2:B25.

Or, enter the formula...

=COUNTIF($A$2:$A$25,A2)

...into B2:B25 and format these cells as...

[=1]"";"Duplicate"

[Brian from Maui]

Try Conditional Formatting.

Go to Format/Conditional Formatting
enter formula is
=countif($1:$100,a1)>1
Set font color or pattern, click OK OK
Copy, highlight rows or columns and go to Edit/Paste Special/Format
All dupes will change to your font or pattern color