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

Remove Duplicate Values

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

12 thoughts on “Quick Tip: Remove Duplicate Values from List

  1. I believe the following would work as well. It won’t matter whether the list is sorted or not.
    However, it references a row above the first record.
    =IF(COUNTIF($C$3:$C$21,C3)>1,IF(COUNTIF($D$2:D2,C3)=0,C3,””),C3)

    And if someone can make this more concise, please let me know.
    Thank you

  2. 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,””)

    • This formula takes the last record it can find, which is great for lists that are not sorted. However, it does not account for the very last record in the list. You will have to include one additional cell in the range:
      D3=IF(COUNTIF(C4:$C$22,C3)=0,C3,””)

  3. Pingback: A Complete Introduction to Excel | MBAx.me

  4. 1) Select your range
    2) Advanced Filter
    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.

  5. Pingback: A Complete Introduction to Excel | MBAx.me

  6. 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!

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

  8. 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.

Post any comments, feedback or questions below!

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s