Creating a variance formula between two different databases-Pivot Table data

[LilianG]

I have two databases from two different sources, I created a pivot table to summarize the data. Then, I copied and pasted values to a new worksheet for sales by cost centers. First, I noticed that there was missing cost centers from one of the databases. I need to compare these two lists to perform an analysis, and create a formula to obtain a variance on sales and quantities. What will be the best way to compare these lists and make my variance formula work?
Second, when I entered the formula, the cell where the formula is located did not perform the calculation; even though the cells are numbers and not text.


How can I resolve this issue using Excel 2010, WIN 7?
Source A Source B Variances

State​

BUID​

CostCntrCd​

Qty ​

Cost ​

​

State​

BUID

CostCntrCd

Qty ​

Cost ​

​

Qty

Costs

California

8412

08412

63​

2,640.80​

​

California

8412

08412

57​

2,590.80​

​

+$D3-J3

=$E3-$K3

California

7430

07430

41​

1,535.74​

​

California

7430

07430

34​

1,465.74​

​

​

​

California

9443

09443

23​

925.31​

​

California

2443

09443

21​

905.31​

​

​

​

California

2446

02446

47​

470.00​

​

California

2459

02459

22​

948.42​

​

​

​

California

4454

04454

19​

190.00​

California

4460

04460

47​

2,173.23​

​

​

​

<tbody> </tbody>

Thanks for the help.
LG

 

[Fazza]

Hello, Lilian

Please can you give some sample input data & the result corresponding to that data? Say half a dozen records from each source.

(A customised pivot table should be able to be created to take the data from the two sources & give you the desired results: the exact requirements are needed to know how to set it up.)

regards

 

[LilianG]

Hi Fazza, the above sample is a result of pivot tables of two different sources(dashboards). What I found out was data from source B had a mix of text and values (Source B info was given to me ) So, I had re-formatted the section of BUIDs converting text to number, multiplying them by one. Then, copy and paste values. I just created a Vlookup table from source A and compare it with Source B, and obtain my variance. Thanks anyway for your interest. LG

 

[Fazza]

cheers, Lilian
regards