Data will change if the total is adjusted

[tropics123]

Hi, I was wondering if this is even a possibility or if anyone has done something like this where they could help me. I receive spreadsheets from our clients (with two columns) the amounts they need us to withdraw from their bank account weekly. and there's a rounding issue. Example, even though their sheet shows two decimal places for Bob as 499.89 (cell A2), the actual amount they typed in cell A2 499.8925. The Total in column A is showing $1,500.57 on their sheet but when we upload their sheet to the bank, the bank only accepts up to two decimal places and with the rounding, the bank's total is $1,500.56. It's off by one penny in this example. In the situation where the client’s total and the bank’s total are off, then we need to adjust by a penny in one of the participant’s amount (we’ll subtract a penny from Bob). When there are thousands of rows and the total is off by $5, we have to go through and manually adjust the amount on each row (picking random people) by adding/subtracting a few pennies to each person's name to get their total to match with what the client wants us to withdraw.

Is there a way, if I type in cell c1 the amount the client wants us to withdraw, which is $1,500.57, is there a macro or formula which will look through the data range in column A and round everything (by two decimal points) and add/subtract amounts column A to match with the client’s total? The difference can’t all go to one participant such as Bob, it has to be distributed by a few pennies to multiple people (if the difference is more than a penny). This needs to be done for both column A and B. The number of rows on the spreadsheets I receive will change depending the on the client adding or removing their employees.

*For the sake of this example, I’m only showing what the bank pulls in for column A, but this is the same issue with column B.



[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Bank picks up for column A[/TD]
[TD] Column B[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]499.8925[/TD]
[TD]499.89[/TD]
[TD]50.61[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]500.0049[/TD]
[TD]500.00[/TD]
[TD]60.89[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]500.669[/TD]
[TD]500.67[/TD]
[TD]20.15[/TD]
[/TR]
[TR]
[TD]TOTAL (client wants us to withdraw)[/TD]
[TD]1,500.57[/TD]
[TD]1,500.56[/TD]
[TD]22.88[/TD]
[/TR]
</tbody>[/TABLE]

 

[JLGWhiz]

Wouldn't it be simpler to go through the client's data and round it to two places before processing it into your system? Then if the original total and new total do not agree, you can make the adjustment at that point for the client who originated the disparity.

 

[tropics123]

That's actually what we're currently doing but when there are thousands of lines (multiple files like this each day), it does take awhile to scan through each one and manually adjust. I was hoping to find a more automated method, but maybe nobody had to deal with something this tedious.