countif formula not working

[TeppE]

Dear MrExcel.

We are using excel to register mac addresses and we need excel to highlight duplicates and give us and notice, telling us that we have a dupe.

we have tried the Countif but that wont work. he have tried 4 different methods of the Countif formula. but non seem to work, even your's wont work.

so dear MrExcel please help

bellowed fan TeppE

 

[Jon von der Heyden]

Welcome to the board.

Can you show us the formulas that you have tried and please explain how your data is setup?

If your MAC addresses are in column A, starting in A2:

In B2: =COUNTIF($A$2:$A2,$A2)

Copy to end of range in column B.

Then auto filter column B. All values of 1 are distinct, everything greater than 1 is a duplicate.

 

[TeppE]

That's exactly what i've been trying

heres a screen shot.

had to type in some random numbers since its against policy to add customer related numbers

if that dident work

http://tinypic.com/view.php?pic=119zfjk&s=7

if tinypic isn't valid for you guys il find something else.

thanks

regards TeppE

 

[Jon von der Heyden]

I don't see a problem with the formula. Perhaps it is a MAC issue.

But if you want to prevent duplicate entry you should change it to:

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

 

[Jon von der Heyden]

Does the formula work when entered into a cell rather than custom validation?

 

[TeppE]

not sure i'l test tomorrow. I'm off work now.

great response time btw.

 

[TeppE]

Ok, we tried i't and i't did not work.
any other ideas?

btw forgot to tell you that we are running excel 2010, if that changes anything