- Okay, so if you're using Google Sheets,
a lot of times you have a lot of data in one worksheet,
and then you have similar data in a second worksheet,
and you may want to just identify which data is duplicated.
So that's what we're going to do right now
we have on the left, let's just say our first work sheet,
and then we're going to look up on the second worksheet
any of the values that may be duplicated.
So I've already highlighted them on the sheet
so that you can kind of follow along
and see what it's looking up and where it's finding it.
And the function that we would use to do that is called
VLOOKUP so let's just make sure cell D-2 is active,
which it is.
Start with an equal sign and we'll just type for you
"LOOKUP," and the first thing that the look up wants to know
is what value am I checking to see if it's duplicated?
So lets select C-2, so we're just going to use last name
because most of the time the last name
is more unique than the first name, in this case
so, we got that.
And then type in the range, and I'm going to make this
just one worksheet at a time, it's gonna work it
a little bit better, and then we'll select the range
so we'll go over to the other worksheet.
I went to the second worksheet and I selected it,
and the thing that we want to do now is,
I'm going to use F4 as a short cut but you want to
fix this range, so it's looking for the last name
and I only wanted to look in this one range
every single time, I don't want this range to move down
because then it'll just have blank rows at the end.
So we want to use F4,
that's going to put dollar signs before every column
and row reference, so every time it looks for something
on the first worksheet, it's still just going to stay
in this range to look for it.
I'll hit a comma.
The index is, okay so, you've highlighted range
for me to look up inside of, which column do you want?
In this case an example that we're doing
we're only using on column,
so just one for the first column,
but if we were to have more than one column,
first one starts at 1 and then it goes to the right,
so it's not looking for A B C D, it's looking for
which number of column in the start of the range.
Use a comma, and then I always put a false for the last
argument, I don't worry about it for now.
Close that off and hit Enter.
So it gives you an error at first because it says
look, I've looked up the last name of Liddon
and I didn't find it,
so let's get that other worksheet up on the screen again
so we can can see.
All right, here's both worksheets,
the one on the left again is looking up the one on the right
let's take this formula, go in the lower
right hand corner of it,
select that little square with your mouse
so we can drag it down.
And we're going to apply this VLOOKUP
to every row in the original table,
and remember when it's looking at the range,
we fixed it so it's not shifting down every single time
it's just looking at the one range on the second worksheet.
So let go of my left mouse key,
and there's errors except for when it tries to look up
these two last names, and these are the two last names
that are duplicated on the second worksheet.
So at this point you could be done,
you have the option of wrapping this in a function
called IFNA, so you would check to see if it's NA,
just make it blank, otherwise run the VLOOKUP function.
That's a little but too complicated probably to
put in a video but the other thing that you can do,
so save this table as a couple thousand rows,
and you can't see all the results, you could just
select columns A though D, go to data
and just select create a filter, and then you would
go to column D, drop down this filter and just say
don't show me the NA's, uncheck that, click OK
and then the only thing you have remaining,
are the rows who are duplicated.
Next up we're just gonna show you a video
about how to highlight duplicates,
maybe you just have one table, but in that table
you have some duplicates, this will show you
how to take care of that, thanks!