Comparing Data sets
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
Attachment | Size |
---|---|
compare-data.xls | 20 KB |
- Nick's blog
- Login or register to post comments
- 14355 reads
Awesome... thanks Almir That
Awesome... thanks Almir
That works a treat.