- [Instructor] Alright, so if you're using Google Sheets,
and you have a table of data like the one that I have here,
and you're having a problem with accidentally
having duplicate entries,
so for whatever reason,
say this is just a list of any inventory,
and you don't want item numbers on here more than once,
there is something that you can do.
And I'll do a little bit of explaining about it first,
so it will be using data validation.
And when we use that data validation,
we're going to use a custom formula with it.
And let's talk about that formula.
So what it will be is a COUNT IF formula.
And we will be looking at the range
that we don't wanna have duplicated.
In this case, it's A6 to A25.
That's where your item numbers are.
And there's a little trick here that we have to do
is we have to fix the row numbers.
So fix the A6 and fix the A25.
And we'll talk about why in one second.
And the test is going to be the value in A6
is in that range.
And you can see by this little blue one here,
that's always a preview of the value
of your function before you hit enter.
This is counting one.
And what that's telling us is A6, which is the value is 5468
is in this range once.
Okay, so if I were to go down and type 5468, again,
you see this value changes to two.
And that's important.
So we're going to use that logic
to write our data validation rule.
So let's be smart and kinda keep this
formula that we created for data validation.
So I'm going to do Control + C to put that on my clipboard.
I'll delete it.
So that's a great way before you do data validation
to make sure that it's working
because it's harder to troubleshoot
inside of this functionality.
Go to data left click on data validation,
and you have this menu pop up.
So data validation kinda lays on top of your spreadsheet.
And what I mean by that is,
it's not something that you can do
by entering a formula into a cell.
It's something that Google Sheets does,
in addition to just what you see in this spreadsheet.
So it's kinda like Conditional Formatting where
it's an extra functionality.
So let's talk about this.
I already highlighted the cell range,
so you don't have to do that.
The criteria here is going to be a custom formula.
So we're going to tell data validation exactly what we want.
And it's giving you a little hint here,
it's showing this is not what's actually in this box.
It's grayed out, but it's a formula
that starts with an equal sign.
So always remember that, so what we put on the clipboard,
I know has an equal sign we'll go look,
it is equals COUNT IF.
It's looking in that fixed range that we talked about.
And it's looking to see if A6 occurs.
Now, this will just give a number.
And that's not enough for data validation.
So we want to give it a condition.
So we wanna say if it's less than two,
the data is valid, right?
So when it returns a one, it means that
that value is just in there once or zero,
it means no values in there, and that is okay too.
So less than two.
Because if it returns a two, or three or four,
that data is duplicated.
You don't want that.
And what we want it to do is not just show a warning,
'cause you might miss that, you wanna reject the input.
So we're saying there's no reason to duplicate it.
So whatever you put in there, if it's duplicate,
just don't let it happen.
And then you can show some text too,
so you would do this if this is a multi user spreadsheet.
So someone else might
need a little hint as to what's going on.
And let's just say
that's a duplicate, pay attention.
All right, let's click Save.
So you can see there's it's actually already been applied.
But since this value was in here already,
it can't prevent it.
But it's giving you a message.
Let's delete that.
And let's see what happens when I try to enter a duplicate,
4784, it won't let you do it.
So once that data validation rule is in there,
you get this window that says, you can't do it.
And then it gives you your little custom message
says that's a duplicate.
So click okay and you're done now,
so you've created a sheet that won't allow
duplicate values in this given range.
And just keep in mind that this is
not a security consideration.
So if you're working in a multi user environment,
and you can't have people entering duplicates,
they can just go in here and erase the data validation rule.
So this is helpful operationally.
But if you have some sort of trust
or security issue going on,
this probably isn't the best solution for you.
And data Validation is useful for a lot of things.
So up next, we're going to look at how it can create
drop downs in your cells.