- Hola data geeks, thanks for joining me.
Today I wanna show you just a little quick tutorial
that I realize I use all the time
and it saves me a ton of problems.
It just really, really helps out
and I thought it might be useful to you.
It's how to actually remove duplicates from a data set.
And so, here what I have is some data in Excel.
The one way I wanna do this is I can look at zip codes.
And what I wanna do is get a distinct list of zip codes.
So, I could do a pivot table.
And I can do that from Insert and Pivot Table.
On my other screen it pops up.
It automatically selects the range, that's good.
Gives me the new sheet.
And then I could do zip code.
And I'll drag it from there down to rows
and I have a distinct list.
Easy enough, right?
So, if I just want a unique list, that's one thing.
Now if I wanted to remove duplicates.
Like let's say this data set,
any time I saw the zip code again
or an order ID again, I would want to remove that row.
I know it's a duplicate.
Well I'm gonna do that, but with zip codes.
So pretend zip codes in this case are order IDs
or something that you wanna find dupes of
and actually eliminate those rows.
So I'm just gonna copy that to a new tab.
And here what I'm gonna do, I'll give it a title,
and I'll say dupe? on the second one.
And it's pretty straight forward.
What I'm gonna do first is I want to order these,
so I'll click on Sort and Filter.
And I'll just add the filter because it's more functional.
And I can click Order and Ascending.
So this is helpful.
Then I'm going to actually do a countif.
So my formula is =countif and the question is,
does this zip code, in this case 1001, exist above me?
So they're in order, they're in ascending order.
So if this zip code exists anywhere above my current row,
that means it's a dupe, or I'm a dupe.
So, here we go.
I'm gonna go =countif now the range is gonna be
all the rows above it.
So it's gonna be A1 to A1, in this case.
And then if it equals this one, A2, give me a count.
So it's going to be a count of zero.
Now if I were to drag this down a little bit
you can see that we have one right here.
So you can see what happened is
it only counted the row right above
so it's actually not doing it right.
See how it's only counting the one right before?
What I actually want it to do is
lock that calculation in to the very first row.
So instead of A11 here and just like I had A1 here
which was fine because I was on the first row,
I'm actually gonna hold Function and hit F4
when I'm selected on that cell.
It adds those dollar signs there.
And the dollar signs indicate,
when I move this formula, don't move that cell reference.
Keep that cell reference locked in place.
And that's important for our formula here.
So now when I copy this down, what you can see,
if I highlight all that and hit Command+D
or Control+D on Windows, as I scroll down
the actual cell reference continues to be the same.
So it's A1 through the actual previous row
of wherever I'm at.
So this works.
So now when I go down to row 12 here,
and let me just make that a little bit bigger for you,
you can see that just before that on row 11
I have that same zip code.
So this one is a dupe.
So I'm just gonna go down to the bottom of my rows here
on the dupe calculation, double click on the little corner,
and it'll copy it all the way down.
And when I go down to the bottom you can see
some of these are, you know, far and above,
there's seven times on the zip code.
So in order to make this an actual binary calculation
to kind of stick true to it instead of having
the total count, like one, two, three, four, whatever,
I'm gonna change this to be an if statement
that will actually return a one if it is a dupe,
and zero if it is not, regardless of how many times
or how many other dupes there are.
So, if my countif here, that statement,
returns something greater than zero,
meaning if it's one, two, three, four,
anything greater than zero, then show me a one,
meaning that calculation that expression was true.
Otherwise show me a zero, meaning it was false,
meaning it's not a dupe.
I hit okay, and notice it's a zero.
When I copy this down you can see row 12 here,
it's still a one.
And when I double click this and it goes
all the way down to the bottom,
so as it creates a calculation and finishes it off,
now when I go down to the bottom I don't see
one, two, three, four, whatever.
I see just one or zero.
Okay, so we're there essentially, right?
Now I'm going to copy this
and I'm going to do a paste values.
That way it doesn't change as I modify this here.
Even though it shouldn't matter,
I just like to do that because calculations,
especially ones like these
where they're dependent upon the order in which the data is
and everything like that,
I just want to eliminate the chance
that there will be an error
when I actually remove some data here.
So now I'm just going to uncheck the zero
and I have, essentially, all of my dupes.
I highlight those, all the rows there,
delete the rows.
Once that finishes its calculation,
go back and clear my filter.
Or I'll just hit clear filter, and I have it.
I have a clean data set.
I just used zip codes here, those could be order IDs,
you could have order and date of something like that.
I do this often with financial data.
We have the amount, the vendor, and the date.
Usually you don't see those things
being duplicated on the same day or at the exact same time.
Things like that really help me a lot
when I'm working with data kind of by hand, here in Excel.
So I hope this'll help you
and if you have any questions about this or anything
feel free to post them down below,
as well as you'll find a link to my blog
and all the other additional information that I have there.
Thanks and I'll see you back here soon.
Hey thanks for checking out my video.
I really, really appreciate it.
Now I also have a blog, bensullins.com
in case you haven't visited that.
On there I have videos like this, I have code samples,
I have full articles describing all the tips and tricks
and everything that I wanna share with you
to help you in your career,
as well as you can sign up for my weekly newsletter
which is basically a digest of all that
plus more stuff I found on the web
that I found interesting that might be pertinent to you.
So you can click this link here and go check that out,
and again I appreciate you watching this video
and I'll see you back here soon.