Prevent Duplicated Entry in a Range

[cyliyu]

Need help in VBA code or data validation will do?
How can I prevent a user from entering a duplicated entry in a range from C6 to J30?
The code if possible should consider a "space" in-between such as 0011 / 1920 and 011/1920. Thanks

 

[herofox]

hi
try this by Data Validation

 

[navic]

Hi @herofox
Good idea, but this formula will not prevent the entry of other data if it is in the row above.
A little correction.

=COUNTIF($C$6:$J$306,C6)=1

Also, a solution via Data Validation will not solve the Copy/Paste problem.

Another idea is to highlight a cell via Conditional Formatting if the user enters a duplicate (or some VBA solution).

=AND(C6<>"",COUNTIF($C$6:$J$30,SUBSTITUTE(SUBSTITUTE(C6," /","/"),"/ ","/"))>1)

 

[cyliyu]

Thanks All.

The formula works fine

=COUNTIF($C$6:$J$30,C6)=1

Except it will not reject 0011 / 1920 and 011/1920 .