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:
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.
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.
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)
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!
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:
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.
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!