I have a series of reports that are generated from company software and exported to Excel. These reports basically consist of balances, payments and adjustments for multiple accounts. Theoretically, Balance - (payments + adjustments) should equal 0. But if there are multiple payments for an account, the report includes a line in Excel for each payment made. Now, just this by itself is not a problem. These balance. The tricky part comes when there are multiple accounts for the same person with multiple payments. This produces incorrect data in the report. Here is a sample of the data, before I continue:

Acct# Name PrevBal Pmt Adj Date
12345 Ann Smith 682.50 -323.86 -341.25 4/24/2014
54321 Ann Smith 318.50 -151.14 -159.25 4/24/2014
12345 Ann Smith 358.64 -17.39 -341.25 4/24/2014
54321 Ann Smith 167.36 -8.11 -159.25 4/24/2014
99877 Joe Jones 514.51 -150.00 -205.80 4/29/2014
99877 Joe Jones 0.00 -158.71 0.00 5/23/2014
65498 John Doe 930.00 -465.00 -465.00 5/1/2014
78965 John Doe 930.00 -465.00 -465.00 5/1/2014
96325 Sally May 407.50 -200.00 -207.50 4/18/2014

To date, the workaround has been to use the formula =ABS(C4)-ABS(D4)-ABS(E4) in cell G4 (copied down the whole list) and when the resulting values for each person's group of account does NOT equal 0, changing the amount in the Prev Bal column to equal 0 for all but the original payments. (Ann Smith in the sample above, we would eventually chance 358.64 and 167.36 both to 0)

I've been playing around in VBA, but am struggling to figure out if there is even a way to put a validation process like this in place, or are we destined to continue to do these manually? Easily a full 1 - 2 days' work, as there are typically 5 reports with over 1,000 lines each.

Thoughts anyone?