Compare two similar tables and determine variances based on three key fields

[pwhatley]

I have two data tables eleven columns in each, with one value or amount column in each. The tables are supposed to have the same totals, but do not. This is due to a problem with a system file that has duplicate entries. The output from the DBA is matching twice and duplicating some records.

I want to summarize each table based on three fields common to each table, compare the summaries and determine the variance based on the three key fields. The approach I took was to create a linked table in Powerpivot for each of the two tables. I then created a calculated/concatenated key field on both table to provide a single key field based on the three fields I wanted to summarize on. I then copied the key field column to Excel, stripped duplicates and pasted those entries to another Powerpivot table. I then linked both tables to the new key field table, created a pivottable with the summary of values from both tables and added a variance column. I had to copy the powerpivot table back to Excel to remove the records that had a zero variance to arrive at a table of variances only.

Question: What am I missing? Is there an easier way to do this perhaps with DistinctRow without creating the separate table and linking to it?

 

[theBardd]

Can you post a workbook to see the extent of the problem?

 

[pwhatley]

Can you post a workbook to see the extent of the problem?

Sorry for the delay. I am being hammered at work prior to monthend and I really had to remove proprietary information from the file.

My understanding is it is not possible to attach a sample file to the post, so, how can I "post" a workbook? Sorry, I'm a newbie.

Thanks!

Paul