Home » Our News » Excel De-Duplication - Option 2

Excel De-Duplication - Option 2
03 March 2015

If the last post explaining the basic Excel Remove Duplicates features doesn't actually do what you want then try using the Pivot Tables. For those who have not discovered the very powerful tool yet is simply takes a set of data list, say, purchase that may include lots of duplicates and near duplicates, and creates a table out of the data.

For example the data sheet shown below has a line for each purchase from our fictitious shop and puts the email address against each. Now some people have bought more than one item and some people have bought the same thing twice.

We can use a Pivot Table (found on the Insert Ribbon) to give us a list of unique email addresses (one line for each). First add a new sheet and then, with the cursor in the A1 cell, hit the Pivot Table icon on the Insert Ribbon.

All you need to do is hit the icon to the right of the Table/Range: cell and go to the sheet where the raw data sits

Mark out the columns you want to report on. If you just wanted a list of unique email address it would be just column A, if you want to show the total number of orders for each email address it would be A and B. If you need a table showing how much each address has been worth then it is columns A to D. Note you select the whole column and not just the range of the data down to the rows. Once done hit the icon with the red arrow to return to the pop-up window and then hit OK

The system shows you where the data will go on the left hand side and gives you a right hand tool bar to manage the data. In this simple case we want to click on the Email entry at the top of the tool bar and drag it to the lower left hand square at the bottom labelled "Row Labels". The system pops the list of email addresses in. If you wanted to see the total number of items ordered you can drag the Item data element to the Column Labels square or down to the Values square. Try each ad see the difference.

Keeping it simple, we have a list of unique email addresses. If you just copy this entire sheet and then use Paste Special - selecting Past Values - you will have a list that you can use for other things. If you save this file we will use it again in another blog exploring how you can merge data from two sheets together. Failing that the link below will download the example file used in the images above.

Example Excel Data File - 2007 (this file is 1MB)
Example Excel Data File - 2003 (this file is 3MB)