richmcgill
Board Regular
- Joined
- Feb 4, 2019
- Messages
- 73
- Office Version
- 2016
- Platform
- Windows
I have to balance loan accounts daily and what would be the best way to do this?
I have worksheet #1 which has a credit and debit side.
I have a report I place on a second tab / Worksheet and add amounts posted to each loan and look for differences in amounts.
Worksheet 2 I need to automatically move Phil and Tony negative amounts with the Loan Number, Customer Name and Negative Amounts changed to a positive number on the Debit side of Worksheet 1.
Worksheet 2 I need t automatically move Joe & George positive amounts with the Loan Number, Customer Name and Positive Amounts and leaving them as positive numbers to the Credit Side of Worksheet 1 .
There are many other loans on Workshee1 and I need this information to stay pure. A Macro or What Formula Best moves the information quickly and accurately?
<colgroup><col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2446;">
<col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3072;">
<col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3669;">
<col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2304;">
<col width="113" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4010;" span="3">
<col width="113" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4010;">
<tbody>
</tbody>
I have worksheet #1 which has a credit and debit side.
I have a report I place on a second tab / Worksheet and add amounts posted to each loan and look for differences in amounts.
Worksheet 2 I need to automatically move Phil and Tony negative amounts with the Loan Number, Customer Name and Negative Amounts changed to a positive number on the Debit side of Worksheet 1.
Worksheet 2 I need t automatically move Joe & George positive amounts with the Loan Number, Customer Name and Positive Amounts and leaving them as positive numbers to the Credit Side of Worksheet 1 .
There are many other loans on Workshee1 and I need this information to stay pure. A Macro or What Formula Best moves the information quickly and accurately?
Worksheet 1 | |||||||
Credit | Debit | ||||||
Date | Loan Number | Customer Name | Amount | Date | Loan Number | Customer Name | Amount |
5/19/2019 | 383894 | Tom | $ 739.99 | 5/19/2019 | 28748 | Kyle | $47.99 |
5/19/2019 | 3456 | Dale | $ 349.98 | 5/19/2019 | 133656 | Skip | $7,834.00 |
5/19/2019 | 6789 | George | $ 392.55 | 5/19/2019 | 28497 | Phil | $699.99 |
5/19/2019 | 87263 | Joe | $ 329.21 | 5/19/2019 | 87263 | Tony | $884.98 |
Worksheet 2 | |||||||
Date | Loan Number | Customer Name | Amount 1 | Amount 2 | Total | Posted | Difference |
5/19/2019 | 64203 | Abe | $199.99 | $333.99 | $533.98 | $533.98 | $0.00 |
5/19/2019 | 87263 | Joe | $2,188.23 | $626.43 | $2,814.66 | $3,143.87 | $329.21 |
5/19/2019 | 28497 | Phil | $3,199.87 | $645.99 | $3,845.86 | $3,145.87 | -$699.99 |
5/19/2019 | 64203 | John | $2,145.32 | $9,834.09 | $11,979.41 | $11,979.41 | $0.00 |
5/19/2019 | 87263 | Tony | $626.43 | $884.98 | $1,511.41 | $626.43 | -$884.98 |
5/19/2019 | 28497 | Al | $645.99 | $645.99 | $1,291.98 | $1,291.98 | $0.00 |
5/19/2019 | 6789 | George | $333.43 | $0.00 | $333.43 | $725.98 | $392.55 |