Need a formula to find Difference

[kittymongo]

Hello,

I have large datasets from a production database to compare to a test database. These are based by state and the data under each state. I need a formula to use in a pivot table to compare the data to show the count and which data is missing. The example below has 4 variables. AK (Alaska) should be total count in Prod plus total count in Data A Turn on in Test Environment minus the total count in Data A Turn Off in Test Environment - this should equal the total count in Stage (last column). If there is a difference, how can I show what the difference is via formula? The difference I want to see is the details under each state (the AAAA, AAAB, AAAC, etc are the details for the listed state). Each state has its own set of data points under it and could be missing one or several of those data points.

If you need more explanation, please let me know. Any help is appreciated. Each state has hundreds of data points and we just need to see what doesn't match up. Thank you.

Prod			Data A Turn On in Test Environment			Data A Turn Off in Test Environment			Stage
State and Data A	Count of Data A		State and Data A	Count of Data A		State and Data A	Count of Data A		State and Data A	Count of Data A
AK	532​		AK	164​		AK	128​		AK	568​
AL	560​		AL	635​		AL	548​		AL	644​
AAAA	1​		AAAA	1​		AAAADE	1​		AAAADE	1​
AAAB	1​		AAAB	1​		AAAB	1​		AAAB	1​
AAAC	1​		AAAC	1​		AAAC	1​		AAAC123	1​
AAAD	1​		AAAD	1​		AAAD	1​		AAAD	1​
AAAE	1​		AAAE	1​		AAAE	1​		AAAE	1​

 

[GlennUK]

So, basically, you have totals and details in the same list every time? That is not logical.

 

[kittymongo]

So, basically, you have totals and details in the same list every time? That is not logical.

We could split it up by state if we need to. But it would be nice to have a formula to locate the differences. Each state has hundreds of data in it. It takes too long to go through each manually when we compare. Any advice on how to streamline this? I'm open to all and any ideas.