duplicate rows

[rds747]

How do I validate a column in excel to make sure it doesn't have any duplicate rows?

 

[firefytr]

Hi there,

You could check out these two excellent sites:

http://members.iinet.net.au/~brettdj/

(The Duplicate Master)

http://www.excelguru.ca/node/24

(by Ken Puls, eliminate dups)

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[rds747]

So there isnt a quick option in excel? I can either write code or download the duplicate master?

 

[firefytr]

No to your first question, yes to your second. There is in the new upcoming 2007 (which is still in beta right now). For the easy option, the Duplicate Master is the way to go. For a more customized solution, Ken Puls code is good.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[rds747]

Thanks for replying, Zack.

Just wanted to make sure that if A1 contained the value '12345' and if A2 contained the value '12345', there is no way that I can have an message box saying cannot enter '12345'.

Thanks.

 

[firefytr]

Oh, yes, of course! It's called Data Validation (from the Data menu). After you remove duplicates from column (I'll say column A), you can add it like this ...

Select column A (A1 should be active)
Click Data | Validation (Alt + D, L)
Allow: Custom
Formula: =COUNTIF($A$1:A1,A1)<=1
Ok

Voila! That's it!

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci