This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Problem: I have a data set in which I would like to find every unique combination of customer and product.
Figure 718 Find unique combinations of customer and product.
Strategy: Although there are several ways to find unique values (advanced filters, pivot tables, Microsoft Query, COUNTIF), Microsoft added a new feature to Excel 2007 called Remove Duplicates.
Remove Duplicates is a powerful feature—sometimes too powerful because it very quickly and destructively removes the duplicated rows.
To use the Remove Duplicates command, follow these steps:
1. Make a copy of your data. Copy it to a new range, a new worksheet, or a new workbook.
2. Select one cell in your data set.
3. Select Data, Remove Duplicates. Excel will display the Remove Duplicates dialog.
4. Click Unselect All. Select Product and Customer.
Figure 719 Choose which columns should be analyzed.
5. Click OK. Excel will confirm how many duplicates were found and removed.
Figure 720 Duplicates removed.
Results: Excel will delete hundreds of rows of data! If you didn’t make a copy in step 1 and you need that data, press Ctrl+Z to undo.
<-Previous Topic Next Topic->
For more resources for Microsoft Excel: