Excel: Checking for duplicate names in a list

TLDR:

Assuming the last and first names are in columns A and B, set up the following formula in C2 and drag it down to the rest of the column:

=SUBSTITUTE(A2&B2, ” “, “”)

Set up the following formula in D2 and drag it down to the rest of the column:

=COUNTIF($C$2:$C$10000, C2)

 

How do these work?

If I have a list of names and I want to find duplicate names, I can use Excel to easily find those duplicates.

names

First, I have to create a new column that has a unique combination of the first and last name. That way Dan and Douglass Dougherty won’t be counted as the same person. I can “add” the two names together and drag the cell down to create this new column.

unique names.PNG

Then, I need to count the number of times each unique name shows up in the whole column. To do this, I will use the COUNTIF function.

=COUNTIF(range of cells that I want to check for the name in, cell that has the name I’m looking for)

count.PNG

Notice that there are dollars signs in the range but not in the part of the function that has the name. As you drag down the formula, Excel will automatically update the cells so that the new cells update to. You want this to happen most of the time, but not with the range because it will stop including cells from above. To prevent it from updating, you can put in the dollar signs yourself, or click on the cell and type the “F4” button and the dollar signs will go in by themselves.

Now, we can filter the “count” column and see the names that appear twice in the list!

fitler

Added twist: what if there are inconsistent spaces?

Sometimes names will be inputted and someone accidentally added a space at the end of the name. Its impossible to tell from just looking at a name whether this is the case but it will prevent the formula from working correctly, as shown below:

space problem.PNG

In order to fix this, we can modify the “unique names” column and use  SUBSTITUTE function.

=SUBSTITUTE(text to search inside, text to search for, replacement text)

In our case, we are going to search for spaces inside the A2&B2 formula. The text to search for is a space, enclosed in quotes (” “), and then we will replace it with literally nothing, just a pair of quotes with nothing inside of it.

space fix.PNG

That way, Excel removes all spaces that you may not necessarily know exist and your task of finding duplicates will not be thwarted by this common issue!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

search previous next tag category expand menu location phone mail time cart zoom edit close