Duplicate Entries


Highlight the cells

Data Validation can be used to restrict the entry of data within a column.
This example shows you how to prevent duplicate entries (both text and numbers) from being entered into a column.

Note that this technique is not restricted to just columns but can also be used with rows and cell ranges (see step 6).


Find the correct formula

You can use worksheet functions in the formulas given for the data validation.
In this example we will use the COUNTIF function.
This function returns the number of non blank cells with a value that satisfies a condition.
We can use this function to tell us if an entry already exists in a particular range.
=COUNTIF($B:$B,B1)=1
(we include the relative reference B1 since we have included the whole column in the formula)


Activate the Data Validation dialog box

Highlight the cells you want to apply the data validation to. In this case the whole of column B.
Press (Data > Validation) to display the Data Validation dialog box and select the Settings tab.
In the "Allow" drop-down box, select "Custom" (or List).
Is there any difference if you entered "=1", "<=1" or "<2".


Enter a duplicate value

Type the following data onto a worksheet and try and type a duplicate entry into column B.
In this example we have tried to enter the text "France" which already exists.
The following error message will appear and the text will be removed from the cell.


Input Message and Error Alert tabs

It is always worth entering a helpful message into both of these tabs.
Input Message - This message will be displayed when the cell is selected (before the users enters any data). This message should be a polite reminder that the data is restricted and should give the user an indication as to what data is allowed.
Error Message - This message will be displayed if the user enters data that does not conform to the validation rules that have been set up.


Using Rows and Cell Ranges

When using functions and especially cell references it is important to understand the significance between using absolute and relative references.
If you wanted to use this technique for rows and cell ranges then the formulas would be similar to the following:
=COUNTIF($A$1:$D$20,A1)=1
=COUNTIF($4:$5,A1)=1


Prevent Duplicate Entries from a Defined List

SS
source formula
=AND(COUNTIF(NamedRange,A1)=1, COUNTIF(A1:A10,A1)=1)



From Drop-Down List - MORE


Highlight the cells

Data Validation can be used to restrict the entry of data within a column.
This example shows you how to prevent a user from selecting the same value more than once from a drop-down list.
Basically once the entry has been selected the item is automatically removed from the other drop-down lists.
Many thanks to Peo Sjoblom and Daniel M for this solution.

This solution is quite advanced and requires a good understanding of both functions and formulas.


Find the correct formula

First create a list of all the items you want to appear in the drop-down list.
In this example we have entered these as a list in column "D" in cells "D3:D8".
Next you need to add a formula that can indicate if the entry has already been selected.
The COUNTIF function returns the number of non blank cells with a value that satisfies a condition.
The ROW function returns the row number of the cell containing the formula.
In this example these formulas are going to be entered in column "E" in cells "E3:E8".
=IF(COUNTIF($B$3:$B$8,D3)>=1,"",ROW())

If this particular entry already exists in column B the cell will appear blank otherwise, the corresponding row number will be displayed.
Drag this formula down to the cells below ("E4:E8").


Using the formula that we have just entered we need to enter another formula.
This formula will create a list of "unused" items containing only the items that have not already been entered into column "B".
The COUNT function returns the number of numeric values in a list or array of numbers.
The INDEX function returns the value from a list based on an index number.
The SMALL function returns the smallest value in a data set.
The INDIRECT function returns a text string of the contents of a given cell reference.
The ROWS function returns the number of rows in a cell range or reference.
In this example these formulas are going to be entered in column "E" in cells "E11:E16".
=IF(ROW(D3)+1-3>COUNT(E$3:E$8),"",INDEX(D:D,SMALL(E$3:E$8,ROW(D3)+1-3)))

The number 3 represents the row of the first item in the list from cell "D3".


Activate the Data Validation dialog box

Highlight the cells you want to apply the data validation to. In this case the whole of column B.
Press (Data > Validation) to display the Data Validation dialog box and select the Settings tab.
In the "Allow" drop-down box, select "List" (or Custom).
Enter the following formula as the Source:
=OFFSET($E$11,0,0,COUNTA($E$11:$E$16)-COUNTBLANK($E$11:$E$16),1)
For more details on these types of formulas, please refer to the Dynamic Named Ranges page.


Enter some values

Select cell "B3". Notice that the drop-down list displays all six items.
Pick "United Kingdom" from the list and then select "B4".
Notice that only the only five items are now displayed.


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext