# Quick Tip: Remove Duplicate Values from List

Many times there are instances when you’ll have a large amount of data and you’ll want to be able to extract a listing of one specific column with only one line for each item type (for example, you may have a listing of all 2,500 customer addresses with a country field and may want to know the full list of countries represented by the customers in the listing).

Here’s a quick and easy method I use to extract the information (and this method is also a small introduction into using logical formulas, which will likely be the next video).  Using an IF function and some sorting, you can extract a listing of information without the duplicate values.

Here’s a screenshot with instructions for how to do it (click to see enlarged version):

Bonus HW assignment: Try to think of other ways that you may be able to get a similar listing without using my method above.

## 8 thoughts on “Quick Tip: Remove Duplicate Values from List”

1. My solution basically lists each item in the next column which appears last time in the list. No sorting needed: D3=IF(COUNTIF(C4:\$C\$21,C3)=0,C3,”")

3) Select copy to another location
4) Pick another location
5) Check Unique Records Only

Done and Done. Best of both worlds. You get to keep the old values, and have a list of the new values that is not separated with a bunch of blank rows.

3. All you guys are awesome, if you have any site/ info/ formal’s like above please pass it on to me, cheers.

4. Let me qualify this before I continue with my comments below. And that is,although I have started exploring Excel (2010) a bit more frequently lately, I’m merely a casual Excel user, Having said that, here is my take on both the solutions.

Each one of the above solutions has its merits depending on what you’re trying to accomplish. Dr. Ransom’s solution of “remove duplicate” button physically deletes the data, that is, removes rows and columns, so the list or spreadsheet gets smaller.

The formula solution, however, preserves the rows and columns and keeps the data formatted so the list looks “clean”, without the loss of data. This solution is desirable for reporting purpose by reducing redundancy and clutter; while the “remove duplicate” button is desirable when only concise list of UNIQUE values is all one needs.

Thanks for posting your solution, I loved it!

Just my two cents!

5. Good point, Dr. Ransom. Hadn’t used that ‘remove duplicates’ feature sitting right next to ‘Text to Columns’.

I think the example still works well as a way to extract data using IF formulas, but you are correct that that would be the simplest way to do it.

One thing this also illustrates is the fact that the more you know/learn about Excel, the more you know you don’t know about Excel. I learn something new almost everyday. Thanks!

6. In Excel 2010 there is a built in function that does this very thing. You highlight your column of data that contains duplicate items. On the data tab of the ribbon, there is a “remove duplicates” button. Select the column that you want to remove the duplicates from. What remains are all the unique values.