Remove duplicate combinations in your data [quick tip]

Excel Howtos - 7 comments

By now, we know how to remove duplicates from data. You can use the Remove Duplicates button to do that.

But do you know that we can use remove duplicates button to get rid off duplicate combinations too?

Remove duplicate combinations – Tutorial

To remove duplicate combinations in your data, just follow below 4 steps:

  1. Select your data
  2. Click on Data > Remove Duplicates button
  3. Make sure all columns are checked
  4. Click ok and done!

See this demo:

Remove duplicate combinations in your data - how to?


How to remove duplicate combinations in some of the columns

If you just want to remove duplicate combinations of Product & Store alone, you can still use Remove Duplicates feature.

This time, select all 3 columns, but check only first 2 columns in the remove duplicate screen. That should do the trick.

Remove duplicate combinations using Excel - tip

More ways to deal with duplicate data

Duplicate data is not a unique problem, its everywhere. That is why at Chandoo.org, we have a plethora of tips, tutorials & videos to help you deal with the problem. Check out below links to learn more.

How do you deal with duplicates?

I use remove duplicates button every time I get raw data.  It is such a time saver. When I want a more automated solution, I use formulas or pivot tables to knockoff the duplicates.

What about you? How do you deal with duplicates in your data? Please share your tips by posting a comment.

This post is part of Awesome August Excel Festival.


Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

7 Responses to “Remove duplicate combinations in your data [quick tip]”

  1. Heather says:

    I have one file that is more database (displaying product data such as warehouse location, description, etc), and that is where I probably deal with duplicates the most.

    Instead of simply deleting them, I need to look for what has changed (or verify that there are no changes). My solution is to sort the file alphabetically, and then highlight duplicates in the item number column. Once I've located the duplicates, I then compare (using formulas) all of the columns to indicate any differences in the duplicate values' rows.

  2. Mustafa says:

    Using advanced filter to create a new distinct version of the data.
    Link the list to MS Access and create a new distinct list from there.

  3. Mustafa says:

    Using advanced filter to create a new distinct version of the data.
    Link the list to MS Access and create a new distinct list from there.

  4. Danny Walls says:

    I need help with loading and uploading csv files for e commerce. How to is most important. Everyone says it is necessary but no one shows you how to do it.

  5. Pranav Anand says:


    I have come across where i have o remove the duplicates from case sensitive data. and i never found any trick or method for this.
    Can anyone guide me how to do this.

  6. Mort Wakeland says:

    About ready to jump out the window. For a week I have searched and searched and not once have I come close to finding any reasonable response or VBA code. This may take an Array, and Advanced Filter, or a Scripting Dictionary - of which I am not fluent in any.

    The issue: I have 6 total columns of data, A:C represents 2014 and D:F represents 2015. The data is consecutive down the rows with A & D being a facility ID, B & E being a facility name, and C & F being a chemical name. The number of rows for 2014 for a facility may not be the same number of rows for 2015. Each row represents a chemical.

    Realizing one cannot compare a single row across all 6 columns (due to varying lengths) I need to compare ABC to DEF and delete duplicates. I want to only keep the chemical that is unique to either 2014 or 2015. Could be one chemical could be several. I should say if unique keep either ABC or DEF, i.e., that row for the unique chemical or rows for unique chemicals.

    Hope that is somewhat clear?????

    Ideas, links, suggestions, recommendations are welcomed with wide open arms.

    Happy Upcoming Holidays,

Leave a Reply

« »