distinct values in column

[parthaemail]

I like to format a column such that it allows only distinct values. fo
eg in the column A i have 10 numbers 1to 10 (A1 to A10) . if i ente
any value between 1 and 10 in A11 it should not allow. A11,A12.....et
should allow only distinct values. pls help me to do this

thanks in advanc

 

[CLR]

Data > Validation > Allow whole number > Between 1 and 10...add error
message if you wish, according to menu.

Vaya con Dios,
Chuck, CABGx3

 

[parthaemail]

hi

the below procedure is not working out, can you please check it out




Data > Validation > Allow whole number > Between 1 and 10...add error
message if you wish, according to menu.

Vaya con Dios,
Chuck, CABGx3

 

[via135]

hi!

Data > Validation > Allow Whole Numbers > Not Between > 1 & 10

-via135

 

[R..VENKATARAMAN]

why not put a list somewhere 1,23,...10 for e.g in g1 to g10
in data validation window choose list(in settings)
and give g1:g10 and error message as not valid.


"parthaemail" <[email protected]>
wrote in message
news[email protected]...

 

[George Gee]

Do you wish to enter UNIQUE values into column A?
If yes, then I think 'Data Validation' will not work.

A clumsy approach would be:
In another column, say B.
In B1 =IF(MAX(COUNTIF(A:A,A:A))>1,"Duplicate","")
Copy this formula down column b as far as you wish.

If you have '8' in A8, and enter '8' in say A12, then
A8 and A12 will display 'Duplicate', until you change one
of the '8' entries.

Any use?

George Gee


"parthaemail" <[email protected]>
wrote in message

 

[George Gee]

Sorry, slight mistake, should read:
B8 and B12 will display 'Duplicate'.....

George Gee

 

[CLR]

Sorry, you're right of course..........I mis-read the post. I see others
have since given you answers more to the point.

Vaya con Dios,
Chuck, CABGx3

 

[George]

Goto cell A then choose 'Data > Validation' from the menu

In the Allow: box choose 'custom'
In the formula: box type the following
=AND(A1>=1,A1<=10,MOD(A1,1)=0,FREQUENCY(A:A,A:A)<=1)
Enter an appropriate error message under the error alert tab
Then copy down to as many cells as needed

The mod function checks for whole numbers
and the frequency checks for any duplicates

hope this helps

George

 

[parthaemail]

hi all

Thanks for all you replies , It is working out.

Thank you experts

 

[parthaemail]

hi

i have a different requirement now, column a should accept only
distinct values. if same number is entered this it should give an alert
in the msg box instead of displaying in the column as duplicate. i think
we have to use macros for this. please help me

thanks in advance

 

[George]

I posted an answer a few days ago but it seems to have not worked

Try this
Goto cell A then choose 'Data > Validation' from the menu

In the Allow: box choose 'custom'
In the formula: box type the following
=AND(MOD(A1,1)=0,FREQUENCY(A:A,A:A)<=1)
Enter an appropriate error message under the error alert tab
Then copy down to as many cells as needed

The mod function checks for whole numbers
and the frequency checks for any duplicates

hope this helps

George

 

[Max]

... column a should accept only distinct values.
if same number is entered this it should give an alert ..

This might work as well ..

Select col A
Data > Validation
Allow: Custom
Formula: =COUNTIF(A:A,A1)<2
Click OK

 

[parthaemail]

hi all

The below procedure is working fine. hearty thanks for valuable
information.



Goto cell A then choose 'Data > Validation' from the menu

In the Allow: box choose 'custom'
In the formula: box type the following
=AND(MOD(A1,1)=0,FREQUENCY(A:A,A:A)<=1)
Enter an appropriate error message under the error alert tab
Then copy down to as many cells as needed

The mod function checks for whole numbers
and the frequency checks for any duplicates