When you extract a large amount of data from a database, the first thing you may want to do is to clean the data and remove duplicates.
In this blog post, I will introduce you to two easy ways to remove duplicate values and get a list with unique records only in Excel.
You can download the practice file here > RemoveDup. It contains a list of all states in United States with each occurring more than once.
The classic way is to use [Filter]:
- Go to [DATA] tab, and click [Advanced] near [Filter]
- In the [Advanced Filter] dialogue box, check the box before [Unique records only] to hide duplicates.
- You can either choose to [Filter the list, in-place] to filter the list in column A and then copy them to a new column, or choose to [Copy to another location] and make a list in another column.
The convenient way is to use [Remove Duplicates]:
- Copy the list to another column – Important!! Otherwise you may just lose your original data
- Select the whole column, then go to [DATA] tab, and click [Remove Duplicates]
- Since we only have one column here, there is no need to select columns, just click [OK]
- This will also give you a summary and help you to find out how many duplicates are in your selected cells
As always, if you have questions about this or other aspects of Excel, please stop by the Weigle Information Commons during my Excel office hours, and I will help to troubleshoot your problem!