Excel remove duplicates | How to summarise list from two sources

I was recently doing some training and one of the issues that came up was that the user had the following problem:

Two stock lists (identical items), two physical locations and he needed to get a summary that showed the total number of items. I first thought about subtotals but when I actually tried it, realised it wouldn’t work. So instead I went via my beloved pivot tables…..

Here is the video and the instructions are below…Now unfortunately for some reason the sound quality recording was so poor that I ended up deciding to record a er, silent movie. (Alas, it can’t really be compared with “The Artist” or “The Gold Rush” ………) but the instructions are also given below…

[flowplayer src=’https://s3-eu-west-1.amazonaws.com/the-excel-expert/Excel_remove_duplicates_silent.mp4′]

Here is the file you can use to practise on:  Excel_remove_duplicates_file

Steps to sum duplicates

1. Create list as normal
   1. If you think you will be adding to this list regularly take this step:

i.    Click on list

ii.    Choose Insert-Table

iii.    On the new ribbon which will appear, click on Summarize with Pivot Table

iv.    After this the steps will be the same as outlined in the rest of the instructions

1. Convert to Pivot table (Insert- Pivot Table)
2. Choose the default settings – i.e. New Worksheet

1. Make sure you are clicked in the pivot table
2. Click on Design – Report Layout – Show in Tabular Form (this gets everything to fit across the page)
3. Choose Subtotals – Do not show subtotals (this removes the headings)
4. Then go to Options – Options – Options
5. Click Display
6. Clear the Show/Expand Collapse buttons (this removes the – from the left of the No. Description)
7. To convert the Unit Cost to currency, right click on the Sum of Unit Cost in the pivot table
8. Choose Value Field Settings – Number Format – Currency