fbpx
Search
Close this search box.

Remove duplicate combinations in your data [quick tip]

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

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:

    Hi,

    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,
    Mort

Leave a Reply