Forum Index
shamskm.com Home
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Prevent duplicate data entry into your excel table

 
Post new topic   Reply to topic     Forum Index -> Technology & How-To Forum
View previous topic :: View next topic  
Author Message
Admin
Site Admin


Joined: 25 Sep 2007
Posts: 268

PostPosted: Fri Jan 13, 2017 12:28 am    Post subject: Prevent duplicate data entry into your excel table Reply with quote

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





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





Click OK.


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






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 Smile

By the way, I am using Excel 2007.




*Recommended:

Read wonderful articles (English & German) on Science & Tech, Environment, Health and many other topics only on BlogArena.


For comments of suggestions, please contact us: info@shamskm.com
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic     Forum Index -> Technology & How-To Forum All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group