A simple task: there is a range of cells (let’s say A1:A10) where the user enters data from the keyboard. It is necessary to ensure the uniqueness of all entered values, i.e. prevent the user from entering a value if it is already present in the range, i.e. was introduced earlier.
Select a range of cells and click the button Data validation (Data Validation) tab Data (Date). In older versions – Excel 2003 and earlier – open the menu Data – Validation (Data — Validation). On the Advanced tab Parameters (Settings) from the drop down list Data type (Allow) choose an option Other (Custom) and enter the following formula into the line Formula (Formula):
=COUNTIF($A$1:$A$10;A1)<=1
or in English =COUNTIF($A$1:$A$10;A1)<=1
The meaning of this formula is simple – it counts the number of cells in the range A1:A10 equal to the contents of cell A1. Input will be allowed only in those cells where the resulting number is less than or equal to 1. Moreover, the range is set strictly (by absolute references with $ signs), and the reference to the current cell A1 is relative. Thus, a similar check will be performed for each selected cell. To complete the picture, you can go to the tab in this window Error message (Error Alert)and enter the text that will appear when you try to enter duplicates:
That’s all – click OK and enjoy the reaction of others 🙂
The advantage of this method is the ease of implementation, and the disadvantage is that it is easy to disable such protection in the same dialog box or by copying and pasting cells with duplicates into our range. There is no reception against scrap. To prevent such terrorist actions, the user will have to enable already serious protection of the password sheet and write a special macro to intercept copying.
But this method will completely protect against accidental input of duplicates.
- Extracting unique entries from a list
- Color highlighting duplicates in a list
- Comparison of two data ranges
- Automatically extract unique items from any list using the PLEX add-on.