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 here for 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.

This entry was posted in Quick Tips and tagged , , , , , , . Bookmark the permalink.

14 Responses to Quick Tip: Remove Duplicate Values from List

  1. D says:

    Hello all, please can anyone explain to me why ” ” was used in the formula?
    Secondly, why reference C2 although it only has “Fruit” written in it?
    Many thanks!

  2. Anonymous says:

    I simply used (=a1=a2). In case of duplicate columns, it returns true else false.

    Apple TRUE
    Apple TRUE
    Apple TRUE
    Apple TRUE
    Apple TRUE
    Apple FALSE
    Banana TRUE
    Banana TRUE
    Banana TRUE
    Banana TRUE
    Banana FALSE

  3. Adrian says:

    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

  4. Ogulcan says:

    The picture is not displayed. I cannot see the instructions.

  5. aaaa says:

    copy the data in another column and apply data, remove duplicates

  6. Adam Wurlitzer says:

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

    • Adrian says:

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

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

  8. Donnie says:

    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.

  9. Swapnil says:

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

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

  11. dhapumdhap says:

    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!

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

  13. Dr Ransom says:

    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.

Comments are closed.