Home » Blogs » Nick's blog

Comparing Data sets

Nick's picture
Submitted by Nick on 28 March, 2014 - 09:51

An excellent way to compare data sets with the same column headings is to list them under each other, and add a new column called "source", and populate that source.

Then create a pivot table, and add source as the column field.

Then you can easily spot differences. Here's an example for comparing Trade PVs

Trade Number PV Source  
Trade1 100 Source1 => First set of data
Trade2 200 Source1  
Trade3 300 Source1  
Trade4 400 Source1  
Trade1 90 Source2 => Second set of data
Trade2 190 Source2  
Trade4 390 Source2  

Create pivot with rowfield = Trade Number, data field = Sum of PV, and Column field = Source then add a column called "DIFF" to calculate the differences in PV

Sum of PV Column Labels      
Row Labels Source1 Source2 DIFF  
Trade1 100 90 -10  
Trade2 200 190 -10  
Trade3 300   -300 => missing trade
Trade4 400 390 -10  
Grand Total 1000 670 -330  

 

You can see the total diff = -330, and Source 2 has a missing trade Download the example file to see how it works

AttachmentSize
compare-data.xls20 KB
»
Almir's picture

Add a calculated field to get differences

Submitted by Almir on 28 March, 2014 - 14:04.

Add a calculated field to get differences when source data changes. calculated field should be Diff Source2 - Source1. Thus, you will avoid manual adjusting formula when source data changes and pivot table is refreshed.

»
Nick's picture

Hi Almir I've never found a

Submitted by Nick on 3 April, 2014 - 14:28.

Hi Almir
I've never found a way to add a calculated pivot field to accomplish this.. V interested if you know a way.
- only works for me if the data is in 2 different cols
tks
Nick

»
Almir's picture

Change PV field settings to Difference from "Source"=>"Source1"

Submitted by Almir on 3 April, 2014 - 15:58.

Hi Nick,

Actually, having this data structure, neither I could. But, there is a way:

In pivot table, change field settings of "PV" from "Sum" to "Difference from". In left window pane chose "Source", and in right windows pane select "Source1".

If you want to show PV value anyway, just add it again to summary section of the pivot table and change field settings as described above.

»
Nick's picture

that must be a new feature...

Submitted by Nick on 3 April, 2014 - 16:22.

that must be a new feature... I have Excel 2010.

»
Almir's picture

It is available in Excel 2003 too

Submitted by Almir on 4 April, 2014 - 07:50.

I have checked it backwards (2007 and 2003): it is available in Excel 2003 too.

»
Almir's picture

I use 2010 too and think it is an old feature but will check

Submitted by Almir on 3 April, 2014 - 18:23.

I use Excel 2010 too, and I and think it is an old feature but will check tomorrow.

»
Nick's picture

can you add the change to the

Submitted by Nick on 4 April, 2014 - 08:16.

can you add the change to the file I added please and post a link!... v excited.
tks

»
Almir's picture

Check it here

Submitted by Almir on 4 April, 2014 - 08:59.

http://excelexperts.com/example-calculated-pivot-field-compare-two-data-...

»
Nick's picture

that's close, but it's

Submitted by Nick on 4 April, 2014 - 10:34.

that's close, but it's missing Source1 and Source2 data.. what I really need is Source1, Source2 and the diffs..

how did u setup the diffs though ?

got a screen shot ?
tks

»
Almir's picture

Check .zip file with demo

Submitted by Almir on 4 April, 2014 - 10:55.

I have uploaded an .xlsx file ("Compare-Data_Almir_v2.xls") with Source1, Source2 and Difference.

I have also uploaded a zipped demo file (.swf). Unzip and run it.

In brief: I added PV field in summary area once again and changed its field settings as desribed earlier.

»