How can i stop data being repeated within a column?
 

I have a list of contract numbers relating to application numbers or
payments. they are in the format nnnnnnan or nnnnnnpn. The columns are fixed
to this format only. If they are entered with the a or p in the wrong place
or if they have been left out completely an error message will appear to
alert the user. I want to know how to alert the user if they enter an
application or a payment number that has already been entered.
ie if they enter 022079a4 but that same application has been entered else
where in the column.
If anyone has a step by step suggestion that would be very helpful. And if
its a formula should i put
it at the top of the column or where. All suggestions gratefully accepted.
Hope you can help

slect the column where the user enter the data
then go to Data--validation-- and select Custom in the drop down choices
and type this
=COUNTIF($E$1:$E$1000,E1)=1
if Column E is being used to enter the info by users

it will give an error message anytime an entry is repeated

"Ru" wrote in message
...
I have a list of contract numbers relating to application numbers or
payments. they are in the format nnnnnnan or nnnnnnpn. The columns are
fixed
to this format only. If they are entered with the a or p in the wrong
place
or if they have been left out completely an error message will appear to
alert the user. I want to know how to alert the user if they enter an
application or a payment number that has already been entered.
ie if they enter 022079a4 but that same application has been entered else
where in the column.
If anyone has a step by step suggestion that would be very helpful. And if
its a formula should i put
it at the top of the column or where. All suggestions gratefully accepted.
Hope you can help

Hi, Thanks for that, I tried it but it still allowed me to enter the same
contract/application number within the column.
The column im talking about is column A. The information entered starts at
A4. I want something to have an error message appear if they try to enter a
application number thats already appeared in the column. The reason is
because two applications/payments with the same number can't exist. each
application number is unique and cannot be duplicated.
ie they are in the format of 022087a4 (application four to contract 022087
or 022094p5 (payment five to contract 022094)....therefore these cannot be
repeated.
The application and payments are linked but are displayed on separate
worksheets within one document.
I hope this makes more sense. If anyone knows a solution please advise me in
a step by step mannar. Thanks alot

"N Harkawat" wrote:

slect the column where the user enter the data
then go to Data--validation-- and select Custom in the drop down choices
and type this
=COUNTIF($E$1:$E$1000,E1)=1
if Column E is being used to enter the info by users

it will give an error message anytime an entry is repeated

"Ru" wrote in message
...
I have a list of contract numbers relating to application numbers or
payments. they are in the format nnnnnnan or nnnnnnpn. The columns are
fixed
to this format only. If they are entered with the a or p in the wrong
place
or if they have been left out completely an error message will appear to
alert the user. I want to know how to alert the user if they enter an
application or a payment number that has already been entered.
ie if they enter 022079a4 but that same application has been entered else
where in the column.
If anyone has a step by step suggestion that would be very helpful. And if
its a formula should i put
it at the top of the column or where. All suggestions gratefully accepted.
Hope you can help