We now have a list of all the places we made purchases (the contents of the Description column) as well as the categories and purchase prices. Many of these places appear more than once. We want a list of where we shop but we aren't concerned about how often we visit each store. We can use the Remove Duplicates dialog box to eliminate these duplicate values.
When duplicate values are removed, they are not simply hidden. They are deleted from the worksheet and can only be recovered by undoing the action.
When removing duplicate values, the active cell must contain data. If a blank cell is selected, the Remove Duplicates tool will not work as expected.
We will be working in the Descriptions worksheet.
The Remove Duplicates dialog box opens:
This dialog box allows us to choose the columns from which we want to remove duplicate values. By default, all columns, Description, Category, and Purchase, are selected. Depending on your version of Excel, the My data has headers checkbox may or may not be checked.
We want to remove duplicate values from the Description column. We will deselect the other columns.
An alert appears letting us know that 45 duplicate values were removed and 69 unique values remain.
Filtering data
Take a moment to examine the remaining values. While there are no duplicate values, there are some descriptions that aren't purchases such as the previous balance, payments, and refunds. To remove these, we'll filter the Purchase column and remove the blank values.
The blank values have been filtered and are now hidden.
Let's use another filter to see just the place we purchased groceries.
We now see the four places we purchased groceries in the month of March.
The Purchase column isn't helpful now. Because we removed duplicate values, only the first time we shopped at a specific store is visible. The Purchase column gives only information about that single visit. However, we used it to filter out the blank values so it is necessary for our worksheet.
The worksheet now looks like this:
