Compare Figures in a Column

C

Cheryl

I need to take a column over 5000 rows long and find out which control
numbers are duplicated. I have tried =COUNTIF($C$2:$C$5000,C2)>1 as an
array in conditional formatting, but it didn't work. If it had worked this
would have been perfect to highlight all duplicated numbers.

Can someone tell me what I did wrong here?
 
J

Jacob Skaria

For highlighting duplicates. Select the range C2:C5000 and try
=COUNTIF(C$2:C$5000,C2)>1

If this post helps click Yes
 
T

T. Valko

I have tried =COUNTIF($C$2:$C$5000,C2)>1
as an array in conditional formatting

That should have worked. Are you sure you applied it properly?

Select the *entire* range of cells starting from cell C2. C2 will be the
active cell. The active cell is the one cell in the selected range that *is
not* shaded. The formula is relative to the active cell.

In Excel versions 2003 and earlier:

Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=COUNTIF($C$2:$C$5000,C2)>1
Click the Format button
Select the desired style(s)
OK out

In Excel 2007:

Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format
Enter this formula in the box below:
=COUNTIF($C$2:$C$5000,C2)>1
Click the Format button
Select the desired style(s)
OK out
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top