Prevent Duplicate Entries In A Range
If you want to prevent duplicate data from being entered into a range, here is a pretty simple solution by using Data Validation.
This formula tells Excel to count how many times the data from the current cell is included in the validation range. If the answer is equal to 1 then the formula =COUNTIF($A$1:$A$50,A1)=1 is TRUE and everything is OK. However, if the answer is not equal to 1, then the formula is FALSE and an error occurs causing the Error Alert.
Instead of typing the cell ranges in the formula, you can select them with the mouse. For the validation range (i.e. A1:A50) select the range, then press the F4 key to make it an absolute reference (includes dollar signs). For the criteria range (i.e. A1) click on the first cell in the validation range (no dollar signs). You do not need to copy this formula. When you select the range first and then apply Data Validation, the validation applies to the whole range. The cell reference you use in the formula is not limited to one column. You can use a range such as $A$1:$C$50.