Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining workbooks with some variable field names | Excel Discussion (Misc queries) | |||
Prevent Hidden Column data from being copied/pasted? | Excel Discussion (Misc queries) | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions |