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 ΧΧΦ·ΧΧ ΧΧΧΦ·ΧΧΦ·ΧΧΧΧ©ΧΦ·Χ (ΧΧΦ·ΧΧΦ· ΧΧΧΦ·ΧΧΦ·ΧΧΧΧ©ΧΦ·Χ) Χ§ΧΧΧΧΧ ΧΧΦ·ΧΧ’ (ΧΧΦ·ΧΧ’). In older versions β Excel 2003 and earlier β open the menu ΧΧΦ·ΧΧ’ - ΧΧΧΦ·ΧΧΦ·ΧΧΧΧ©ΧΦ·Χ (ΧΧΦ·ΧΧ - ΧΧΧΦ·ΧΧΦ·ΧΧΧΧ©ΧΦ·Χ). ΧΧΧΧ£ ΧΧ ΧΦ·ΧΧΧΦ·Χ Χ‘ΧΧ¨ΧΧ’ Χ§ΧΧΧΧΧ Χ€ΦΌΧΦ·Χ¨ΧΦ·ΧΧ’ΧΧ’Χ¨Χ‘ (Χ‘Χ’ΧΧΧΧ ΧΧ‘) from the drop down list ΧΧΦ·ΧΧ’ ΧΧΧ€ΦΌ (ΧΧΦΈΧΧ) Χ§ΧΧΧΦ·ΧΧ ΧΦ·Χ ΧΦΈΧ€ΦΌΧ¦ΧΧ’ ΧΦ·Χ ΧΧ’Χ¨Χ’Χ¨ (ΧΧ ΧΧ) and enter the following formula into the line Χ€ΧΦΈΧ¨ΧΧΧΧ’ (Formula):
=COUNTIF($ΧΦ·$1:$$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 ΧΧ’ΧΧͺ ΧΦΈΧ ΧΧΦΈΧ (ΧΧ’ΧΧͺ ΧΦ·ΧΧ’Χ¨Χ)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.