Unique values in one colum
Posted by David De Kegel on September 05, 2001 7:30 AM
Hi,
I hope someone can help me with this.
I want to enter numbers in one column.
The trick is that none of the values can be the same in that column.
Example : I enter 5 in A1, 8 in A2 and when I trie
to enter 5 again in A3 a sort of message is displayed that this value is already used.
Thanks in advance,
David
Posted by Mark W. on September 05, 2001 7:51 AM
David, you can use the Data | Validation... menu
command to enforce your rules. For example,
begin by select column A and tab so that cell A2
is the active cell. Choose the Data | Validation
menu command; set the "Allow" setting to "Custom";
enter the formula, =AND(A2<>$A$1:A1); click on the
"Error Alert" tab and enter an appropriate message
(e.g., Hey, no duplicate values!!!); and press [ OK ].
Now you can't enter duplicates in column A.
Posted by Mark W. on September 05, 2001 8:15 AM
If you devilish data enterers...
...who relish the opportunity to circumvent your
validation rules by entering text representations
of numeric values (e.g., '7) you may want to
restrict them if a truly numeric value (7) already
exists -- just modify the validation formula
as follow: =AND(A2+0<>($A$1:A1+0))
, you can use the Data | Validation... menu
Posted by Don on September 05, 2001 8:33 AM
The function COUNTIF will help you here. Two ways to go ...
1) Entry by entry, assuming the list starts in A2 and goes down some variable number of rows - in B2 enter =COUNTIF($A:$A,A2)
and copy the formula down to the bottom row. Each time you make an entry, copy the formula in column B to the new row. Column B will return a "1" for unique entries and a number larger than "1" if the number already is in Column A.
If you want to test a number before adding it to Column A, make a little "work area" in B1 and B2. In B1 you'll type the number under consideration. In B2 key the formula =COUNTIF($A:$A,$B$1). If the number is unique, B2 will return a "1" and you can go ahead and add it to column A.
