Prevent Duplication

[August]

Is there a way to prevent someone entering a duplicate number/text entry in a range of cells in a spread sheet. If not to prevent them, then display a message, as in a validated list, that the entry they are trying to make is a duplicate.


Thanks

 

[P Sitaram]

Select the entire column (say col A).

In Data Vaidation|Custom, enter the formula:

=COUNTIF(A:A,A1)<=1

Modify the error message to your liking.

 

[August]

Works perfectly, but I don't understand how!!!!
Usually I can see how solutions work (even if I could never think of them myself)

Thanks

 

[P Sitaram]

Got to some cells of the column and check th data validation formula. The `A1' part would have changed to that cell reference. What this means is that the validation criteria is checking if there are more entries in the column that match the current cell entry.

 

[August]

Got it........Thank you

 

[MENANCY]

If I may expand on this subject -
You tip is wonderful for cells where the data is "typed in".
Is it possible to do this and use a pulldown list at the same time?

 

[August]

Got it........Thank you

How would you do this for more than one column?

 

[August]

Got to some cells of the column and check th data validation formula. The `A1' part would have changed to that cell reference. What this means is that the validation criteria is checking if there are more entries in the column that match the current cell entry.

How would you do this for more than one column?