tropics123
Board Regular
- Joined
- May 11, 2016
- Messages
- 85
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.
<tbody>
</tbody>
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.
Column A | Bank picks up for column A | Column B | |
Bob | 499.8925 | 499.89 | 50.61 |
Mary | 500.0049 | 500.00 | 60.89 |
Mike | 500.669 | 500.67 | 20.15 |
TOTAL (client wants us to withdraw) | 1,500.57 | 1,500.56 | 22.88 |
<tbody>
</tbody>