I am looking for some direction in how to make a project easier than what I'm making it (Smarter, not harder, right?)
I have a spreadsheet where I have all my GL entries and I need to find:
1) Any job # that has an anomaly,
2) The month in which that anomaly happened,
3) The amount of the anomaly
In the sample attached, I've created 3 pivot tables which show some anomalies. Essentially what I need to see is anything where the entries in specific accounts do not net to zero in the same month.
Real data differences:
1) There are about 12 accounts I will be reviewing. SOMETIMES the amounts in the main 3 accounts may be due to other accounts having a balance.
2) There are about 50 jobs
3) The start date and end date for each job is different so the months where each individual job should net to zero will all be different.
One known issue causing one of the problems:
My WIP-84 document was posted in November, when it should have been posted in October causing the problem with job # 0013. There are likely other WIP documents in other months causing the same problem where the higher sequential document was posted in the wrong month.
The problem with the sequential document is the primary factor in my anomalies.
Examples:
1) WIP0000062 did not have the sequential 63 posted for my job-0013
2) WIP0000084 was posted in November, while 83 was posted in October (I believe this was the true cause of my issue on job #0013)
3) Before November, additional sets of 96 & 97 were posted
I need to identify those items as discussed above to review them to see the affect.
Thank you for any assistance you can provide to help me think of another way I may be able to do this more efficiently.
Bookmarks