Part 2 of our Excel Guides series. Read Part 1, Part 3 and Part 4 here.
If our last Guide explaining the basic Excel ‘Remove Duplicates’ features doesn't do what you want, then try using Pivot Tables. For those who have not discovered the very powerful tool yet, Pivot Tables simply take a data list, say, ‘purchases’ 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. Some people have bought more than one item.
Step 1
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.
Step 2
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.
Step 3
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.
Step 4
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.
This automatically compiles duplicate data into one section, making it easier to find.