Prevent duplicate data entry into your excel table

Forum to talk about all the latest technologies - ins and outs!!
Post Reply
Admin
Site Admin
Posts: 325
Joined: Tue Sep 25, 2007 12:41 pm

Prevent duplicate data entry into your excel table

Post by Admin »

Suppose you want to make a list of members with names and unique id numbers for your newly born 'Samba' club. So you would like to prevent double entries for member names.


Let's start:

Select the range (A2:A15)

Go to Data> Data Validation

Data Validation window opens:

Under Settings > choose Custom  from Allow

Enter the following formula under Formula:

=COUNTIF($A$2:$A$15;A2)=1


Image


Under Error Alert type Alert: Duplicate Entry. In the Error message box type: Stop!! It's a double entry!!


Image


Click OK.


Now everytime you will want to make a duplicate entry in your member's name, the following error message will pop-up


Image



Few words on COUNTIF function

COUNTIF function takes only two arguments - a range, and a criteria. So its syntax is

COUNTIF(range, criteria)

So when I enter =COUNTIF($A$2:$A$15;A2)=1, the COUNTIF function returns a TRUE if it finds the criteria A2 (or Phillip Mayer) within the given range ($A$2:$A$15) and allows the criteria (Phillip Mayer) to occur only once (=1) within the range. So, if you try to enter Phillip Mayer second time, it gives you an error message.

What about Milly Bauer in cell A6? How it works in this case as in the formula only cell A2 is given as criteria. Well you have selected the whole range before you went to data validation, so the formula is copied automatically to other cells. Just select the cell A6 or Milly Bauer > now go to Data > Data Validation. In the window you will see the formula takes cell A6 as its criteria =COUNTIF($A$2:$A$15;A6)=1.

Have fun :-)

By the way, I am using Excel 2007.
Plakend1972
Posts: 1
Joined: Sat Aug 31, 2019 10:53 am

Re: Prevent duplicate data entry into your excel table

Post by Plakend1972 »

thankyou so much for the poic because i have started my data entry work yesterday and I losted that many times but now I got trick from you to prevent that. Actually, my client give me some data entry work for the website www.resumeshelpservice.com and I was very stressed about the lost of my data back to back but now I will be able to prevent that. Thanks for the help.
Beauvais
Posts: 4
Joined: Tue Sep 01, 2020 8:50 am

I am very enjoyed for this blog

Post by Beauvais »

I am very enjoyed for this blog. Its an informative topic. It help me very much to solve some problems. Its opportunity are so fantastic and working style so speedy FOR MORE DETAILS.
Post Reply