Find Duplicates in Other Column
Learn how to efficiently find duplicates between two columns in Google Sheets using conditional formatting and the MATCH function. This tutorial simplifies the process of verifying attendance against registration lists.
So I wanna find duplicates in another column, meaning I have two tables of people's names. I have those who registered for an event, and I have those who attended an event, and I wanna make sure those who registered actually attended, or I wanna see those who attended actually registered, vice versa. We're gonna look for duplicates. Basically, we wanna say, "Hey, does this Marcia Garcia, does she show up in this attended? Does Robert Johnson show up?" And I don't want to have to do Command+C, Command+F, fi-- okay, Robert Johnson was there. Okay, Michael Williams, uh, was not there. Each one… I don't wanna do that each one, one by one. I wanna do this very simply with some conditional formatting . We're gonna get there. I'm gonna highlight the B column because I'm looking for each of these
names in the D column, for example. I'm gonna go up to Format , Conditional formatting . Over here on the right, I have the range B1 to B9995. I can change this to B colon B if I want. Optional. And I'm gonna Format rules , go to Custom formula is . We're only gonna do Equals, MATCH , and we wanna do B1 because we're starting in B1. Even though there's no name there, we wanna start there because the range starts there. We're gonna do comma and then D colon D. Now, this is the column where you're looking for the match. Okay, we have the match in the B column, but we're gonna look for the match in the D column. And then what I like to do is comma zero. This, in the MATCH formula, just says exact match.
If you're looking for fuzzy matches, there's other options, like one or two here. But for my case, I want an exact match . I'm gonna do end parentheses, and right away, I see that something is highlighted. I'm gonna change this to a very garish purple, so I can see exactly, really quickly, oh, all of these people atten- actually did register and attend. Great. And I can see who didn't attend. Fantastic. There you go. Equals, MATCH , B1 to D colon D, and then use an exact match , and end parentheses, click Done, and we are on the way. Anyone who end up, ends up actually being added to the list, this will be updated automatically. Let's go to the bottom, add Michael Williams, and there.
Michael Williams already is highlighted. Fantastic, right? You are watching better sheets here on YouTube. Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how tos, get more out of your Google sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is gonna be your next Google sheet.