Comparing Data sets

Nick's picture

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
Nick's picture

Awesome... thanks Almir That

Awesome... thanks Almir
That works a treat.