RichKat
New Member
- Joined
- Nov 8, 2009
- Messages
- 39
- Office Version
- 365
- Platform
- Windows
We are assigning accounts and departments to expenses on a monthly credit card statement. Each Row/Record has a debit column (G) and a Credit column (H) and the row also has an Account# Column (K) and a Department column (L). This looks something like this:
Then I have a separate table that looks like this:
In this table I want to scan down the account and department columns and when the account and department match one of the cells in my table, I want to subtract H from G and add it to the total in that amount cell.
So for the first line in the first table, G-0 is 26 and it would be placed in the total next to the fifth cell from the top’s amount.
Any help would be much appreciated.
Thank you
User | Transaction Date | Description | Debit | Credit | Category | Account | Department | |
Brian | 8/18/2023 | MOUNTAINS EDGE | 26 | Repairs/Maintenance | 66200 | 100 | ||
Gabe | 8/19/2023 | RBT MARATHON PETRO1014 | 1 | 200 | ||||
Gabe | 8/19/2023 | RBT CIRCLE K # 23710 | 0.32 | 200 | ||||
Gabe | 8/22/2023 | CIRCLE K # 23706 | 50.02 | 200 | ||||
Gabe | 8/22/2023 | WALMART.COM | 39.47 | 200 |
Then I have a separate table that looks like this:
Department 100 | Department 200 | |||||
Account | Amount | Account | Amount | |||
50001-100 | 50001-200 | |||||
65000-100 | 65000-200 | |||||
65500-100 | 65500-200 | |||||
65600-100 | 65600-200 | |||||
66200-100 | 66200-200 | |||||
66425-100 | 66425-200 | |||||
67150-100 | 67150-200 | |||||
67400-100 | 67400-200 | |||||
67575-100 | 67575-200 | |||||
Statement Total | $ - | |||||
In this table I want to scan down the account and department columns and when the account and department match one of the cells in my table, I want to subtract H from G and add it to the total in that amount cell.
So for the first line in the first table, G-0 is 26 and it would be placed in the total next to the fifth cell from the top’s amount.
Any help would be much appreciated.
Thank you