Ensuring unique entries with data validation rules

- We're looking at a worksheet called, "Data Validation." The Data Validation capability in Excel is a powerful one. And in this worksheet, imagine we're about to put some ID numbers in column B, and one of our big concerns is we don't want any duplicates. In other words, an ID number can't be entered more than once. We wanna prevent that possibility from occurring. Data validation is different from many other data management tools, because it's all about data before it goes into a worksheet. And you can base data validation on rules that revolve around the use of functions.

Now there's a function in cell C2. Normally, when you use this feature, you don't display a function out here, and you might not even be familiar with the COUNTIF function, but I'm going to put in some numbers in column B before we use this feature. 456, 789, 456, 780, so on. It's obvious we've duplicated an entry here. Now, if we had a bunch of entries and wanted to know if there were duplicates, one technique would be to use a function here like what we're seeing, COUNTIF.

Now, if I were to change this to be cell B2, and for the moment, we don't want this to be equal to one, although later in Validate, we will use this, so what are we about to do here? We're saying, in effect, by way of the COUNTIF function, looking in column B, how often does the current entry, the one we see right here in B2, how often does that appear? And if we were to get rid of the leading spaces here, and actually press return, it occurs two times. Drag this down the column or double-click the lower right-hand corner, either way.

This appears once. This appears twice. I'll put in another number, and another number, and another number. This one is a duplicate, therefore, 2. So in each case, the COUNTIF function is adjusting to the appropriate cell, and counting how many times this appears. Now, if we use the Data Validation capability, we will be able to say, in effect, using this function, we want to make sure that this is always equal to one. Now, you don't necessarily prepare the function ahead of time outside, but I'm going to do that.

I'm simply going to highlight this and press Command C and escape. And I'm going to get rid of all this data here. Typically, when you use Data Validation, you're starting with a blank slate, not always, because data validation really can't do anything with existing entries, so let's say we're starting over here, Column B. We go to the Data tab, and the term is Validate. The option is Validate. Click the button. We can allow, under the Settings tab, Any value, Whole numbers, Decimals, we can refer to a List, it can be a Data entry, and we can put in some requirements there, too.

Timeframe, Text length, Custom means custom formula, and that's what we're about to use here. So I'm going to paste in here what I initially copied, and make a change, put = in front of it. When you write formulas in Data Validation, you use the active cell as a substitute for the others, so I'm putting in B1 here. You can click it or type it. So, anytime we make an entry here, we're gonna compare it with other entries in the column. We wanna make sure that the count that that entry appears in there, the number of times it appears, is always equal to one, and if it's not, we've got an error.

It will prevent the entry. I'm going to highlight this, press Command C, because I wanna paste that on the worksheet for reference. Click OK. There we are. So we'll put in a number. By the way, these can be numbers, they can be text, or any kind of mixture thereof, and we didn't have any restriction about the length of these either. Oops, what happened there? It gives me opportunity, not valid. I've already got that number in there. If I click Cancel, it will delete the number. If I click Retry, it will give me a chance to edit. Maybe I shouldn't have had the final 8.

There we go. The formula that I used there, and I'll paste it out here, and this is not something you need to do, but I'm putting it here for reference, again, is this. Even though B1 itself contains text, we simply use this to indicate any cell, even though it doesn't look like that, that's what we use in our validate formula. This is what we use here. So any entry we put in here, if it's all letters, here's ASDFG, you know where that's coming from. Here's another ASDFG.

What's happening? That's not acceptable, whether it's numbers, whether it's text, or any mixture, this works. One of a number of different techniques you can use to control data entry by way of Data Validate. The dialogue box says Data Validation, the button itself says Validate, same capability.