halp

File: don-draper.jpg (130KB, 1260x840px) Image search: [Google]

130KB, 1260x840px

I have data that needs to be reconciled. There's two parts to the process money coming in n and money accounted for. Whenever there's a variance between the two it shows up on my diagnostic tools and I investigate. It's sorted by date deposited then by a code. There's 12 different codes. In a 30 day month with 12 codes there's 360 independent instances that need to balance.

Money coming in comes in as Deposits/Transfersin/carriedpriormonth. This creates a positive effect for any given deposit date for a particular code. Money accountant for comes in as posts/transferout/carriednextmonth and has a negative effect.
ie
Deposit 1/15/17 code A $100
Post 1/15/17 code A $80
-20 variance need investigation (20 dollars was missed while posting)

Each type is on an excel sheet with it's own tab ie post has its own tab deposits has it's own tab.

Given this information is there a way to create a pivot table to easily reconcile and tell what is causing the variation?

We have about 100 new transactions every day split between the 12 codes.

Would it also be possible to use a relational database or would the amount of new items make it not effective.

Thanks!