MrExcel.com - Your One Stop for Excel Tips & Solutions

Use Validation to Prevent Duplicate Data Entry

January 25, 2024 - by

Use Validation to Prevent Duplicate Data Entry

Problem: I want to prevent people from entering the same receipt number twice.

Strategy: Use the Custom form of Data Validation to prevent duplicate data entry.

Someone has entered four receipt numbers in A2 to A5. After typing a duplicate receipt number in A6, the data validation says Duplicate Value - This Value Already Appears Above.
Figure 1484. 1234 has already been entered and is invalid.

It takes a bit of planning to make this work. In the example, the first receipt number is entered in A2. You want to prevent duplicates in A3:A100. You have to craft a formula that works for A3 but has the correct dollar signs so it can be applied to A4 through A100.

=COUNTIF(A$2:A2 uses an expanding range. When evaluated from the point of view of A3, this reference is saying to look at everything from row $2 to the row just above the current row. Copy the reference down and it will be looking at =COUNTIF(A$2:A99 when evaluated in A100.

You want to see how many times A3 was entered in A$2:A2. If this value is a duplicate, the COUNTIF would return a number larger than zero. Thus, the test to see if the new entry in A3 is not a duplicate would be =COUNTIF(A$2:A2,A3)=0.

Follow these steps:

  • 1. Select A3:A100

  • 2. Choose Data, Validation or press Alt+D, L

  • 3. In the Allow dropdown, choose Custom

  • 4. In the Formula box, type =COUNTIF(A$2:A2,A3)=0

  • 5. On the Error Alert tab, use a Title of Duplicate Value and an Error Message of This Value Already Appears Above.

  • 6. Click OK.

In the Data Validation dialog's Settings tab, choose Allow: Custom. In the Formula box, use =COUNTIF(A$2:A2,A3)=0
Figure 1485. Prevent duplicate entries with this formula

This article is an excerpt from Power Excel With MrExcel

Title photo by Amir-abbas Abdolali on Unsplash