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:
- Select your data
- Click on Data > Remove Duplicates button
- Make sure all columns are checked
- Click ok and done!
See this demo:
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.
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.
- Advanced & array formula approaches:
- Pivot Tables & other techniques:
- Highlighting duplicate entries:
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.
7 Responses to “Remove duplicate combinations in your data [quick tip]”
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.
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.
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.
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.
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.
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
@Mort
I'd recommend asking this in the Chandoo.org Forums
http://forum.chandoo.org/
Attach a sample file with some data if you can