Data Comparison in Workbooks

R

RWilliams

I have 2 workbooks with the same layout – one contains weekly details of
Project Resource 'Requirements' (in days) and the other one contains details
of Team Resource 'Availability'

Name Project 01/01 08/01 etc…
Fred P1 3 2

I would like to compare the 'Requirements' with the 'Availability' and
calculate the variation ..

Name Project 01/01 08/01 etc…
From WB1 Fred P1 3 3
From WB2 Fred P1 4 2
Variation +1 -1

I tried producing a Pivot Table from the combined sheets but the results are
incomprehensible.. any thoughts on how this might be achieved would be
gratefully received ….
 
A

AKphidelt

Hey, i haven't tested it out, so this might not actually work. But what I
would do is created a dummy column called workbook. And on workbook 1, just
put something like wkbk1 in the first cell and copy that down to the bottom.
Then do the same for workbook 2 and name it wkbk2. Copy that data and put it
in either workbook one or create a new page for the combined data. Create a
pivot table with. Then format it with the workbooks on top. And whatever you
want to the left, like name, day, etc. Then right click on the pivot table
and go to Table Options and turn off Totals for Columns and Rows. Then you
just have one long set of data and you can create another dummy column that
gives you the difference... so it would look like this...

--------------------------------Workbook
---------------------------------1-----2
Fred--P1 ---- Sum of 01/01---3----4
--------------Sum of 08/08---2----3

Just a thought
 
R

RWilliams

Great thought .. this has created most of what i want to do ... i just need
to add the calculation for the difference between the 2 figures

what i have now using the extra row (as suggested) and a pivot table:

01/01 08/01 etc
Proj1 joe demand 5 3
supply 3 4
sue demand 3 3
supply 3 2

Can i get the system to calculate the difference between the 2 figures? I
would like:

01/01 08/01 etc
Proj1 joe demand 5 3
supply 3 4
variation 2 -1
sue demand 3 3
supply 3 2
variation 0 1
 
Top