No duplicates in column?

[gillweb]

I'm trying to set a column as a "loan number" and make sure it is not repeated. The one vaildation I found as a tip on this site didn't work for me. I'm not sure if it's beacuse I use alpha-numeric values and not just numeric?
ColumnA
1234C5
2345C6
etc.
Any ideas?

 

[Aladin Akyurek]

On 2002-11-06 11:21, gillweb wrote:
I'm trying to set a column as a "loan number" and make sure it is not repeated. The one vaildation I found as a tip on this site didn't work for me. I'm not sure if it's beacuse I use alpha-numeric values and not just numeric?
ColumnA
1234C5
2345C6
etc.
Any ideas?

Let A2:A15 be the range of interest for input.

(1.) Select A2:A15.
(2.) Data|Validation.
(3.) Choose Custom for Allow.
(4.) Enter the following as formula...

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

(5.) Click OK.

 

[gillweb]

worked like a charm. thank you much.

Is there a way to easily check if there are already some duplicates? Or would the validation automatically tell me there are duplicates?

 

[Aladin Akyurek]

On 2002-11-06 11:53, gillweb wrote:
worked like a charm. thank you much.

Is there a way to easily check if there are already some duplicates? Or would the validation automatically tell me there are duplicates?

It won't tell you anything about the entries that were entered before you set up data validation...

You can check for them with conditional formatting using the formula...

=COUNTIF($A$2:$A$15,A2)>1
This message was edited by Aladin Akyurek on 2002-11-06 12:04

 

[gillweb]

Great, thanks for your help!