Skip to main content

Restricting User to enter Duplicate Values using Data Validation

Hello Everyone,
Some times we come across situations where we want to restrict duplicate values to be entered. For example in the attached file, we want to avoid entering duplicate invoice numbers.

It can be possible with DATA VALIDATION Custom Formula:-

=COUNTIF($C:$C,$C1)=1 

Here are the steps:-

1. Select the target column fully say Col C.
2. Click Data tab in the Ribbon, select Data validation.
3. In the Drop down, select Custom.
4. Enter the Formula and click Ok.

It will give an error message while entering first duplicate.

Explanation:-
The Target column which has been selected, is the Range on which Data validation applies. Excel interprates above formula in context of Each cell in the Target Range and decides whether that cell has Unique or Duplicate entry. The $C1 parameter of Countif formula is a Row relative range. So when excel moves to next cell downwards in the Target Range, $C1 changes to $C2 and so on, for each next cell. When user puts a duplicate in the cell and hit enter, excel immediately interprates above formula in regard of that cell (by changing $C1 accordingly) and throws a warning message as count of that cell value in the whole C column will become 2 which is not equal to 1. So condition =1 will get falsified and excel will throw error warning. This is how it works.

Note:- Copy and pasting the same Invoice No., will not generate any error. Hence, you need to enter the Invoice No by editing the cell.

Check the attached file:-

Labels:

Comments

Post a Comment

Popular posts from this blog

Automation:- Sending Invitation to Meeting Using Excel VBA

Hello Everyone, In one of the previous post, I wrote about automating Sending Emails using Excel VBA. This time I have come up with a pretty similar code. The code below sends Outlook Meeting Invitations to recipients on one click. here is the Code:- Sub Send_Invite_Auto() Dim olApp As Outlook.Application Dim olApt As AppointmentItem Set olApp = New Outlook.Application 'Creating Outlook Session Set olApt = olApp.CreateItem(olAppointmentItem) 'Creating an Appointment With olApt .Subject = "Enter the subject here." 'Subject .Start = DateAdd("d", 5, Now) 'Enter Date + Time here. .Recipients.Add ("example@gmail.com") 'Recipient Name, Alias, or any other Attribute. .MeetingStatus = olMeeting 'olAppointmentItem with Meeting status olMeeting 'becom...
10 comments
Read more

Making Password Protected PDFs using Excel Vba and PDFtk Tool

Hi Everyone, This time, I have come up a VBA Code to generate Password protected PDFs using Excel. Actually, Excel Vba has .ExportAsFixedFormat Method to generate PDFs but this hasn't any Parameter which takes password to protect the PDFs. So I have used PDFtk Tool which provide Command Line Interface to make PDFs protected using Password. Actually, You can do various things using PDFtk Tool command line varying from creating, merging, Protecting and many other. So Download the PDFtk Tool from the following link:- Download PDFtk Tookit Here are the Steps:- 1. Install the PDFtk Toolkit. 2. Use the following code to Print or Export the Activesheet with a password.
8 comments
Read more

Highlighting Duplicates across multiple sheets

Hi One and All, This time I have come up with some conditional formatting stuff. The aim is to highlight the duplicates across multiple sheets and with in the sheet as well. Assumptions:- 1. I am assuming that sheet names goes on like sheet1, sheet2, sheet3.... 2. The Target Column No. is same in all the sheets. I mean, as in the attached example, its Column A which is being targeted in both sheets.
2 comments
Read more