No duplicates in column?

gillweb

New Member
Joined
Nov 5, 2002
Messages
8
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?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Nov 5, 2002
Messages
8
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
 

Forum statistics

Threads
1,144,437
Messages
5,724,356
Members
422,546
Latest member
KevinOnTheDaily

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top