Problem: I have a data set in which I would like to find every unique combination of customer and product.
- 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:
- Make a copy of your data. Copy it to a new range, a new worksheet, or a new workbook.
- Select one cell in your data set.
- Select Data, Remove Duplicates. Excel will display the Remove Duplicates dialog.
- Click Unselect All. Select Product and Customer.
- Choose which columns should be analyzed.
- Click OK. Excel will confirm how many duplicates were found and removed.
- 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.