I have 2 tables (table1, table2), each with 2 categorical variables/columns (product, country) and up to 5 real-valued variables to be reconciled/compared against each other (sales and other in the example below). Datasets can have unique categorical variables, e.g. only table2 has product = hats or only table2 has country = India for a product. There are also blank rows.

As far as I know, a pivot table with multiple consolidation ranges is an ideal tool for my purposes of reconciliation. I have drawn an example of input data (two ranges) and wanted output (pivot table). As you can see, the pivot table displays both categorical variables, then repeats values for the first variable in both tables, then uses calculated fields with their difference and the same for the second variable. The last column gives a user an insight on whether the entry is consistent/reconciled.

I know how to start a session with multiple consolidation ranges (ctrl+d, p), but I find it impossible to use two row labels (product, country) within it, let alone display values from both tables and use calculated fields for differences and reconciliation status/result. Any tips?

pic: http://s28.postimg.org/xcerx7919/inputoutput.jpg

data: http://leteckaposta.cz/456590863


If you are open to an addin, I would recommend the free PowerQuery addin from Microsoft. This would allow you to consolidate the two tables using a merge query that you can then crosstab.

You need to start by changing your two tables into Excel tables. Then you can use PowerQuery to query each table. Then link the 2 together & add new columns for the diff's.

Worst case would be that you would have to add a new column joining your two index columns but the whole point of PowerQuery is that it is an ETL (Extract/Transform/Load) tool so it allows you to do these kind of transforms in stages.

  • I would much rather use default tools of Excel than add-ins as the sheets I'll be generating are to be read by several users. However, I'll give PowerQuery a try. Could you describe this solution a bit more? – regresss May 30 '15 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.