How to Delete Duplicate Text Entries in Excel

Next Video:
How to Sort Sheets in an MS Excel Workbook....5

Deleting duplicate text entries in Excel is especially useful if you're working with a very large list. Delete duplicate text entries in Excel with help from a mechanical engineer with 32 years of experience in a large aerospace company in this free video clip.

Part of the Video Series: Using Excel
Promoted By Zergnet


Video Transcript

Hi, I'm Ted. Today, I'm going to show you how to delete duplicate text entries in a range of cells in Excel. This is something that's useful to know how to do when you have a very large list and it would be very tedious to go through and do this kind of thing manually. So what I have is a very simple list, it's a list of cities and I want to eliminate all the duplicates. Let's say we want to maintain the original order. So first thing we're going to do is we're going to insert and I often do this in Excel just for a variety of reasons. We're going to insert a column here and we're just going to add the number so that we can go back when we're done and get back to our original order. Because the original order might be important. So I could have done this beforehand but there we go. So the key to doing this the first step is to sort the list by the entry that you want to eliminate duplicates on. So we're going to select columns A and B and we're going to sort and we're going to sort by the city so we click on city here and we say OK and there we go. The cities are not organized alphabetically and as you can see sure enough there are some duplicates. In fact there are quite a few. So now here's the next trick is to make yourself another column and that column is going to have an if statement and it's going to test whether each entry is a duplicate or not. So we're going to call the heading duplicate question mark and then we're going to go of course the first one is not a duplicate so we say no and then the next one we're going to basically test whether each entry is the same as a previous one or not. So we're going to use the if statements. We're going to go equals if and if B3 equals B2 then with comma we're going to enter if it's equal than it's a duplicate so we're going to type in a yes. Close quotes and if it's not equal then it is not a duplicate and we type an N that's supposed to be a closed parenthesis. And there we go. Now let's just copy the formula down to a few of them and test whether this is working OK. So this one is not a duplicate, this one is a duplicate. Scroll down a little further and we get to Brooklyn and we see a bunch of duplicates. So now we just click on the last one last formula, move the cursor to the bottom right, double click and sure enough it copies the formula all the way down to the bottom. So I'm going to select the column column C and I'm going to say copy and then I'm going to paste special and I'm going to paste as values. And this makes sure that the formulas are gone now but the values if you go to one of these cells it's a value not a formula anymore. Then the final thing we want to do is we want do eliminate the duplicates. So we're going to select all three column and we're going to sort by column C which is where we now have the code that says whether it's a duplicate or not. We're going to go to data, sort and we're going to sort by duplicates and we want the Y's to appear on top. So since Y appears after N in alphabetically, we want to sort from Z to A. We go OK and sure enough all the duplicates appear on top and then the final thing we can go do is just go through those cells very easily now they all appear in one chunk here. We select ll those rows, select them all, select edit, delete. All the duplicates are gone. Everything left is nos and what we're left with in column B is a complete list of all the unique values only. So I hope this has been helpful. I'm Ted and today I showed you how to delete the duplicate entries in a range of cells in Excel. Thanks for watching.


Related Searches

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!