00:00 Hey in this video, I hope you get some cool little tricks and tips on how to deal with this kind of thing, where we discover that we have duplicates, but they might be misspelled.
00:12 Um, we have some interesting things in Google sheets that we can use, but they sort of fall short. So I'm going to show you how to do this with formulas, but first let's talk about the problem, right?
00:25 You have some statuses, maybe this is like a CRM you've put into sheets and maybe some people have added manually.
00:32 So you're not using any data validation to do this, which if you watch my other videos, you'll see, you'll see a data validation you can do to fix this problem before it even starts.
00:41 But let's say we have this problem. We have people who are typing in send instead of scent or send in weird ways, send it.
00:51 Uh, and they have like followed up in two ways where it's like up uppercase or lowercase. Um, everybody has their own system and they're just typing in stuff.
01:01 Now we have this same problem maybe with statuses in a CRM or maybe names, right? Uh, we can type in names maybe from a bunch of meetings.
01:10 Uh, you'd try to digitize a list or something. And you know, Betsy becomes Betty a couple of times and you want to be able to find where did somebody mess up or very quickly?
01:23 Is there any misspellings? Maybe there are none. And you just want to double check. Now what you might know, and you might search for is something called the duplicate detector.
01:33 But the duplicate detector inside of Google sheets would go up to data. We go to data cleanup, remove duplicates. We can do this, right.
01:43 We can remove duplicates to quickly see if some, if there's anything that's wrong, right? It won't delete anything that's like differently spelled.
01:55 So if we just do this, it says, great. We have 10 unique rows. Awesome. But now, now we have Betsy and Betty here, but I don't know if you just saw what happened.
02:06 It deleted everything else. It's a very destructive thing. So we don't want to do that. Right? We don't want to destroy our sheet.
02:16 Uh, you might have to, you know, duplicate this sheet and do it on another sheet, but like, it always seems a little scary to like destroy something and, and take something from hundreds of rows or thousands of rows.
02:28 And if you've been doing this for a while into like 10 rows and you're like, oh no, where did all my other data go?
02:34 Uh, so I want to show you something very quick that you can do to figure out, is there any, uh, misspelled duplicates so we can do literally equals unique.
02:48 And in this range, in this parentheses, we're going to do a colon. A and that gives us the exact same result as what we just did with the destructive element destructive way.
03:00 We now have the list and now we can clearly see Betsy is here and Betty is here, but let's say, even if you do this, you have dozens of rows.
03:09 And you're like, Betsy and Betty are way off. And these are the same people, right? We want to fix this, but maybe there's like dozens or hundreds of unique values we can add in front of it, a sort.
03:22 And we can sort that by column one. So we do sort parentheses unique in here as a range comma, one to sort the column, the only column we have is one.
03:32 Then we add another comma and now we want to know, is it ascending or descending? Is it going from a to Z or Z to a, uh, a to Z is ascending.
03:41 So we say true. And we have Betsy and Betty together. And now we can clearly see, uh, with our human eyes, right?
03:49 A computer knows that these two things are different, but we know, oh, this is the, this is the same. So we can say, oh, which one of these is supposed to be correct?
03:56 Probably you might not necessarily know exactly who is like the exact correct one. So let's just do a count F and like, let's see how many times these appear, because maybe the majority of times they appear will show us, Hey, that's the correct one.
04:14 Maybe we don't have like a, uh, the original document to look at. So we look at count. If we go a colon, a and our criteria is going to be the C1.
04:25 And now we see assets to auto-fill. Okay. Let's auto fill it. So we don't have to type in anything else.
04:31 But here 262 times, we have Betsy and only twice we have Betty avocado, all Savannah, not avocado here. We also discover we have fine here.
04:42 So again, let's go through this. This is w was really easy. Just a few things we had to remember, which was unique, a colon, a and we wrapped that with sort.
04:59 Then we do column one and we can also do false. If we want to. Now that's going to be descending from Z to a, there we go.
05:08 Same thing. Now we got count. If the range is going to be a colon, a C1, it's going to ask us if we want to auto-fill let's say yes.
05:20 And same idea. We have 263 Betsy's and one Betty. Great. Now all we have to do is say, okay, there's only one Betty here, so we can command C copy, command F to find in the sheet.
05:36 And there we go. There's Betty. There's the only Betty we know of. And we can just name them Betsy. And if we start typing, boom, we got auto complete.
05:44 So now we know, oh, and this also disappeared from this unique list automatically. So we know we fixed it, right?
05:50 So that duplicate detector is only going to be able to over here in data, data, cleanup, this removed, it's only going to be able to delete everything, right?
06:01 Delete all the unique stuff, or the only keep the unique, delete all the copies. It's not going to let us actually fix it right now.
06:09 This allows us to fix it and know if we fixed it, we can do the same here with fine. So we might not know that there's a fine here.
06:19 And also look at this. We can scroll through this. We're not gonna be able to quickly see that there's hundreds of fins here.
06:25 And maybe somebody misspelled. One of them they did the secret is they did, but how do we find it out?
06:31 We do sort unique. A colon, a co the sort column is one, let's say true this time. True for ascending.
06:42 We have Betsy and we also have a Betty, and we also have fine and Finn. Okay. We don't know which one we need to delete.
06:49 So we count if the range is a colon, a criterion C one, and auto-fill yes, please. Now we have a fine, where is that fine?
06:59 Oh my God. It was 263 road, 2 63 hoof. Thank God. We didn't have to actually go through all of these and double check.
07:09 Now, one thing I want to share with you, one caveat, one roadblock you might get into, let's do it with our statuses.
07:18 We want to do the same with statuses. We've typed in these statuses by hand for decades. No, maybe a year or two.
07:24 And we have hundreds, right? We have these emails that we've sent out. We have a little CRM in a Google sheet.
07:29 We want to clean this up. Now, watch what happens when we do unique. This is going to be a little bit similar, but the same, uh, a colon a now we have sent to send, send, send sended, followed up, followed up.
07:46 These two are exactly the same, except they're capitalization of up and follow up here. This is a unique to the unique function or formula where it thinks of these two as different, but let me show you one issue.
08:03 That's going to happen. So let's do the same. And we're going to sort a same as we did before sort sort column one.
08:11 True. So we're going to have this followed up and followed up together. Now, how do we find out which one is where, where are they?
08:18 This is a little harder. And we're going to get a little bit of an error. And we're not. You got to just like, know that this happens.
08:25 So count. If the range, same aid, a criterion C1, same. We're going to auto-fill it. But check out, followed up and followed up when they are misspelled just by capitalization two and two.
08:42 Why is that? Well, the CA the count, if we can see, they are the only two, these are the only two, and they're both counted as two it's because the, if is not looking at capitalization, it thinks these two are exactly the same.
09:02 So there's a couple ways to fix this. If you notice this at all in your data, and you're like, oh, man, this county is not helping me.
09:08 It's actually making a much harder, uh, there's a couple of ways to fix this. And I will show you thusly.
09:16 One is, let's say we're searching and we can't find the difference. We can do a command F we can type the tip.
09:24 Then I click this more options, find and match case. So we use match case. In this case, we want to search this sheet.
09:35 We want to find, and there we go. We find only one, no more results found looping up. It is only finding one.
09:42 Great. We can fix that one right now. If we fix it immediately goes away and we still have two here.
09:50 Great. We now know we fixed it. Okay. But there's one other way. Um, just in case you happen to know like one issue might be, Hey, you have the state of the cleanup.
10:01 They're all typed in different ways. There are maybe it, it, the issue is that there's a lot of uppercase and lowercase and MIS not just misspelled with the letters, but uppercase and lowercase.
10:12 So here's how we do it. We can do lower. We have a function called lower and we do a one.
10:20 Great. And we can auto fill this, right. But I'm not going to auto fill this. I'm going to show you an easy way to get everything.
10:27 And we just want one formula sometimes. Yes, sure. Let's copy paste everything. And we'll get this one, followed up, followed up it, it brings everything down to lower.
10:40 We can do the opposite too, if you wish, if you want a everything upper upper case definitely do that. We can definitely, definitely do that, but I don't want to have to copy paste this stuff.
10:53 I don't want to have to rely on, uh, this, uh, I would just want one formula and here, I'm going to show you a re formula.
11:05 This is cool. So array formula let's do lower. And instead of a one, or in addition to Avon, we're going to do a one colon a, because we need an array, uh, array formula.
11:18 We absolutely can do this. You're not going to get an error if you do a one here, but you're not going to get the good stuff out of it.
11:25 You're going to get just one, one cell, but we want the entire array, the entire column of aid, which is an array, a one to a, and now in one formula, we get everything.
11:39 Instead of having to copy paste, instead of having to do anything else, we get one formula and we have now everything.
11:46 All right. Now, instead of our sort unique co a column, a we can do column B and this shows up so clearly that, okay, here's followed up too.
11:59 It's not doubled up. And if we do a unique, uh, sort range, a unique A eight a, Now we can clearly see, we can look at this unique of VB column and this unique of the, a column, and clearly see, we know, oh my God, here's our issues.
12:28 Oh, we can clearly see this followed up is doubled up here. Oh, this is easier to deal with. This is really easy data, uh, cleaning here.
12:37 Oh, that's awesome. So we got unique. We can sort different unique, uh, uh, res or columns. Oh my God, this is so much easier and so much better than having to do destructive, uh, duplicate detection and do other things.
12:55 I hope this was very helpful to you. We got to learn the unique function, formula sort formula and array, formula, and lower and upper.
13:06 And I hope this was really helpful to you to detect duplicates. That might be misspelled, might be misaligned or even miscapitalized, uh, as a bonus here.
13:17 Thanks for watching. Bye.