MrExcel.com - Your One Stop for Excel Tips & Solutions

Remove Duplicates

October 26, 2022 - by

Remove Duplicates

Problem: I have a data set in which I would like to find every unique combination of customer and product.

There are five columns of data. Product is in B. Customer is in D. You want to find unique combinations of Product and Customer.
Figure 775. 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.

In the Remove Duplicates dialog, click Unselect All. Then choose the checkboxes for Product and Customer.
Figure 776. Choose which columns should be analyzed.

  • 5. Click OK. Excel will confirm how many duplicates were found and removed.

A message box says 482 duplicate values found and removed; 81 unique values remain.
Figure 777. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Juan Domenech on Unsplash