How to Find Duplicates

About this Tutorial

Learn how to find duplicates with a simple formula in Google Sheets.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

CONCATENATE() FILTER() IF() MATCH() ROW()

Video Transcript

<div> Hello, sheet shakers and formula fanatics. We're gonna find duplicates. We're gonna de-dupe your sheet today. How we're gonna do this. Well, this video by the way, is gonna save you 20 bucks. Why do I say that? Because if we wanted to remove duplicates and use this able bits. . Add-on this, add-on costs $19 and 99 cents per year, or $59 per lifetime subscription. You don't need to get this, obviously, if you wanna use all of able bits. Stuff. This might be a really good buy, but I'm actually gonna show you how to de-dupe using a formula right now.

And a couple of actually really better ways to de-dupe because honestly, you can de-dupe with Google Sheets itself or get all remove your duplications. I'll show you that right away. Have say a list of names and see at the bottom we have a bunch of duplicates. If we just highlight that, we go up to data . , we go to remove duplicates . We use just, let's say column A removed duplicates. It'll say 10 duplicates found removed, 50 rows, and they just removed them. Didn't tell me which ones, didn't tell me what happened or why it removed them. It just removed them.

That's good if you want to just do that. But sometimes we would rather just show our duplicates. We wanna. What got duplicated, who duplicated it? Why, why what to do next. There's a lot of reasons why we wanna do that. So we may, let's just undo that and here we go. We have our IT back. So today, what I'm actually already showed you that. , I'm gonna show you how to show the duplicate and which row it's in, because sometimes just knowing and, and saying, Hey, this is a duplicate might not be enough.

We wanna know where is that duplicate . We might have hundreds, thousands, tens of thousands of rows and data that is imported by a Google sheet or sorry, a Google form . Or maybe there's like eight people putting in information and they're all putting in the same information at the same amount of time. And maybe we don't even. The first one. Maybe we want the second one, third one, fifth one. Depends. So there's lots of reasons why we might, might want to only show that there are duplicates. So I'm gonna show you that in a formula .

And if you pay for better sheet right now lifetime access , one flat fee, I'll never charge you again. I'm actually going to do one more difference. So we're gonna do this today. I'm gonna show you how to do this buy better sheets. Right now. I'm gonna show you how to create a little duplicate filter and a little sort of data duplicate detector to show you in this nice little formatted. Table where , what, what's duplicated and what row it's in. So I'll show you that as well when, if you pay for a better sheet. But right now, let's, let's go and build this.

This is a pretty complicated way to do this, but I wanna show you how it's done. So you learn something right away. So we have. Column of data . We have these names and we wanna know which ones are duplicated. Well, there are absolutely a couple ways, right? I showed you how to use the remove duplicate function in Google Sheets. You can sort right this, this sheet or this even. You can sort just this column go up to data and sort range . Absolutely you can do that.

Then you'd have to scroll through, see which ones are duplicated. say, we wanna just know if something is duplicated, the way is to use match . So we go equals match . And what we're doing is we're searching for the cells in this column, comma, and we're searching through the entire column and we wanna say the search type. I always like to use zero. There's a few other reasons why you might not wanna use zero, but most of the time I use zero. And it's gonna show us the row it's on. And we can copy and paste this all the way down.

We can go down here and if you notice right away, right here, chance Rodsky shows up and the row is not the same. So 51, this should have been 52 phone pasting and we have now not the row that they're on. So what this match function is doing is it's searching through this and saying wherever the first match that is exact, bring me back the row number , it's on. Okay, so. These don't match . We wanna show, hey, this is a duplicate and then we can also show which row it's on, right? The original one where it is. This is really fascinating and we can do this with a simple if, if function .

If we do. Equal, let's do equals if. And now our row, we can get through a row function filter or function formula . Just row two parentheses here. And that's going to say, that's going to get us the exact row we're on right now. So if these two things match , everything is fine. So let's say if true, right, do nothing if false, which is the third argument . , we're gonna want to know what roon, so we actually don't say the row. We say, Matt, we do the same thing, match a 54 A to a zero. So we do exactly. Same thing, but we just wanna know the actual row. So we do that.

So now if there is, if the row that it's on and the match function are the same, it's doing nothing because we don't wanna know that we wanna, we just wanna know the ones. that are same, right? So now we can scroll through and we can see, oh, here's a number , and then we can also add, let's do this, let's do a connate. Yeah. Let's give a, let's give ourselves a little message. Duplicate , found on row not space. Now. Now we have Gulu get found on row 35, right? So now we can point ourselves to where the row is, right? So now all we have to do is scroll through this. And find this.

Now, if this is hundreds or thousands of, of rows long you may want to invest in a add-on or go ahead, pay the one-time fee for better sheets.co. And I'm gonna show you how to build a little bit of a filter , a little table that shows you all of these, shows you what row they're on, shows you what the information is, and just makes your sheets a little bit better. So go check it out Better sheets.co. But hopefully this helped you. This if function row equals match and you can con, concatenate and make any kind of message you want duplicate found on row x, which is now match .

So this match function helps you greatly find duplicates. And that's it. Now I saved you already 60 bucks. Go ahead, check out better sheets.co for way more features way more cool filters and formulas and really quick tricks and tips to make your Google sheets.</div>