Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Beginner
- [Instructor] This data is a mess and when we learn how to control this kind of stuff, we are really working with Excel. So check this out. We know that we don't have 19 people. There are some extra records in here. Let's take a look. We look at Odeda Mays Koller. Odeda Koller. Rows two and nine. Same birthday but then the city is called Devlin and it's called Devlin Park. We go down and look. Terri Benton. T Benton. Probably the same person. Our job is to identify the possible duplicates. It's up to you if you want to make a new column, flag in the possible duplicates. Maybe you want to use conditional formatting. But just identify the possible duplicates. Right? Pause the video. Work it out. Come back and I'll show you a way through this. Alright now. You ready to get into this? This is beautiful. What we're going to do is create a unique ID. I'm going to just call it UID. I want to put this into a table. Home. Format as table. Give me some room. Get rid of the filter buttons. Now we got to think about what's really consistent here. The start of the cities. The birthdays are consistent. The names. So let's do this. Equals. I'm going to do left. Open parenthesis. City. Comma. Three. I'm grabbing the first three letters of the city. After ends for and. Let's get the last three letters of the last name. Right. Open parenthesis. Last name. Comma. Three. Close parenthesis. And. Birthday. And. Let's get the first initial of the first name. Left. Open parenthesis. First name. Comma. One. Close parenthesis. Look at that. We've stitched together unique IDs for everybody. Let's sort. Let's go to data and then sort. Now look at rows five, six and seven. Terri, Terri and T. In the unique ID column, that person's IDs are identical. So what I'm going to do now. I'm going to highlight this column. Home. Conditional formatting. Highlight cells rules. Duplicate values. I'm going with the default. Okay. The red cells are our possible duplicate entries that we can look at. Marsha Lynn Cray and Marsha Lynn. Probably the same person. But we can see that now and we did not have to go eyeballing and scrolling around and guessing.