how to avoide duplicate entries in to cell

[Anand]

How to avoide dulicate entries in a column while entering the data.
And how to fix the cell length to max of 10 characters length

 

[Shane Devenshire]

Hi,

Are these conditions in the same range?

A. To prevent duplicate entry setup the following Data Validation:

1. Highlight the range, lets say A1:A100
2. Choose the command Data, Validation
3. Under Allow choose Custom
4. Enter the following formula in the Formulas box:
=COUNTIF(A$1:A$100,A1)=1

B. To limit the number of characters entered in a cell:

1. Highlight the range and choose Data, Validation
2. Choose Text length from the Allow drop down
3. Choose less than or equal to from the Data drop down
4. Enter 10 in the Maximum box

One thing to keep in mind - if the user copies and pastes data into the
range where the Data Validation is, it is wiped out and anything can be
entered.

to combine both in the same range
1. proceed as in the first example but use the following formula
=AND(COUNTIF(A$1:A$100,A4)=1,LEN(A4)<=10)

 

[Jacob Skaria]

Try the below

From menu Format>Conditional Formatting>
Select the column say col I
Condition1
Formula Is:
=COUNTIF(I:I,I1)>1
and select a color from Format button.

For restriction on length
menu Data>Validation>
Allow 'TextLength'
Data 'less than or equal to'
MAximum '10'

If this post helps click Yes