The_Pretender
New Member
- Joined
- Aug 20, 2019
- Messages
- 1
Hi All
Our accounts software accepts bulk upload of journals from excel, the format is as below:
[TABLE="width: 980"]
<colgroup><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Batch Date[/TD]
[TD]Batch Description[/TD]
[TD]Batch Type[/TD]
[TD]Main[/TD]
[TD]Location[/TD]
[TD]Nat[/TD]
[TD]Dept[/TD]
[TD] Amount [/TD]
[TD]Description[/TD]
[TD]Journal Code[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD]Bank Payments August[/TD]
[TD]N[/TD]
[TD="align: right"]164500[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]00[/TD]
[TD] 678.26 [/TD]
[TD]MOTL61[/TD]
[TD]T10[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD]Bank Payments August[/TD]
[TD]N[/TD]
[TD="align: right"]511900[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]0910[/TD]
[TD="align: right"]05[/TD]
[TD]- 678.26 [/TD]
[TD]MOTL61[/TD]
[TD]T10[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD]Bank Payments August[/TD]
[TD]N[/TD]
[TD="align: right"]164500[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]00[/TD]
[TD] 633.52 [/TD]
[TD]COML57[/TD]
[TD]T10[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD]Bank Payments August[/TD]
[TD]N[/TD]
[TD="align: right"]511900[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]0910[/TD]
[TD="align: right"]05[/TD]
[TD]- 633.52 [/TD]
[TD]COML57[/TD]
[TD]T10[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD]Bank Payments August[/TD]
[TD]N[/TD]
[TD="align: right"]164500[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]00[/TD]
[TD] 186.50 [/TD]
[TD]WELD06[/TD]
[TD]T10[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD]Bank Payments August[/TD]
[TD]N[/TD]
[TD="align: right"]511900[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]0910[/TD]
[TD="align: right"]05[/TD]
[TD]- 186.50 [/TD]
[TD]WELD06[/TD]
[TD]T10[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD]Bank Payments August[/TD]
[TD]N[/TD]
[TD="align: right"]164500[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]00[/TD]
[TD] 243.02 [/TD]
[TD]DUMP37[/TD]
[TD]T10[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD]Bank Payments August[/TD]
[TD]N[/TD]
[TD="align: right"]511900[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]0910[/TD]
[TD="align: right"]05[/TD]
[TD]- 243.02 [/TD]
[TD]DUMP37[/TD]
[TD]T10[/TD]
[/TR]
</tbody>[/TABLE]
In the above, a positive amount is a debit and a negative is a credit. In the above, our leasing liability account is 164500 while the bank account is 511900.
What I'm looking to do is prepare a leasing payments schedule that can generate these entries automatically. An example of what I mean would be the below table being our "control" where payment date and amount are entered from our bank statement:
[TABLE="width: 218"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Payment Date[/TD]
[TD]Payment Amount[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]678.26[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]633.52[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]186.5[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]243.02[/TD]
[/TR]
</tbody>[/TABLE]
In another sheet in the workbook, I would then like the journal to pull the description from a separate workbook and generate the above journals, ready to be uploaded. The vast majority of our leases are different values, so 678.26 should easily pick up that it's MOTL61 as that is the only lease of that value. There are a few leases which are for the same values, but these are for related items and are always collected on the same day e.g. if we have for leases for 500 per month, all four will have different descriptions (MOTL1, MOTL2, MOTL3 & MOTL4) but all will be collected on the same day. Is there a way of including a third column above, so that if multiple payments of the same amount are collected then these will all pull across the correct references?
[TABLE="width: 326"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Payment Date[/TD]
[TD]Payment Amount[/TD]
[TD]If multiple entries at same value, how many? [/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]678.26[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]633.52[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]186.5[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]243.02[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]
If any of the above could be implemented it would greatly speed up my monthly bookings, so any help with this would be much appreciated.
Our accounts software accepts bulk upload of journals from excel, the format is as below:
[TABLE="width: 980"]
<colgroup><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Batch Date[/TD]
[TD]Batch Description[/TD]
[TD]Batch Type[/TD]
[TD]Main[/TD]
[TD]Location[/TD]
[TD]Nat[/TD]
[TD]Dept[/TD]
[TD] Amount [/TD]
[TD]Description[/TD]
[TD]Journal Code[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD]Bank Payments August[/TD]
[TD]N[/TD]
[TD="align: right"]164500[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]00[/TD]
[TD] 678.26 [/TD]
[TD]MOTL61[/TD]
[TD]T10[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD]Bank Payments August[/TD]
[TD]N[/TD]
[TD="align: right"]511900[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]0910[/TD]
[TD="align: right"]05[/TD]
[TD]- 678.26 [/TD]
[TD]MOTL61[/TD]
[TD]T10[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD]Bank Payments August[/TD]
[TD]N[/TD]
[TD="align: right"]164500[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]00[/TD]
[TD] 633.52 [/TD]
[TD]COML57[/TD]
[TD]T10[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD]Bank Payments August[/TD]
[TD]N[/TD]
[TD="align: right"]511900[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]0910[/TD]
[TD="align: right"]05[/TD]
[TD]- 633.52 [/TD]
[TD]COML57[/TD]
[TD]T10[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD]Bank Payments August[/TD]
[TD]N[/TD]
[TD="align: right"]164500[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]00[/TD]
[TD] 186.50 [/TD]
[TD]WELD06[/TD]
[TD]T10[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD]Bank Payments August[/TD]
[TD]N[/TD]
[TD="align: right"]511900[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]0910[/TD]
[TD="align: right"]05[/TD]
[TD]- 186.50 [/TD]
[TD]WELD06[/TD]
[TD]T10[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD]Bank Payments August[/TD]
[TD]N[/TD]
[TD="align: right"]164500[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]00[/TD]
[TD] 243.02 [/TD]
[TD]DUMP37[/TD]
[TD]T10[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD]Bank Payments August[/TD]
[TD]N[/TD]
[TD="align: right"]511900[/TD]
[TD="align: right"]0000[/TD]
[TD="align: right"]0910[/TD]
[TD="align: right"]05[/TD]
[TD]- 243.02 [/TD]
[TD]DUMP37[/TD]
[TD]T10[/TD]
[/TR]
</tbody>[/TABLE]
In the above, a positive amount is a debit and a negative is a credit. In the above, our leasing liability account is 164500 while the bank account is 511900.
What I'm looking to do is prepare a leasing payments schedule that can generate these entries automatically. An example of what I mean would be the below table being our "control" where payment date and amount are entered from our bank statement:
[TABLE="width: 218"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Payment Date[/TD]
[TD]Payment Amount[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]678.26[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]633.52[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]186.5[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]243.02[/TD]
[/TR]
</tbody>[/TABLE]
In another sheet in the workbook, I would then like the journal to pull the description from a separate workbook and generate the above journals, ready to be uploaded. The vast majority of our leases are different values, so 678.26 should easily pick up that it's MOTL61 as that is the only lease of that value. There are a few leases which are for the same values, but these are for related items and are always collected on the same day e.g. if we have for leases for 500 per month, all four will have different descriptions (MOTL1, MOTL2, MOTL3 & MOTL4) but all will be collected on the same day. Is there a way of including a third column above, so that if multiple payments of the same amount are collected then these will all pull across the correct references?
[TABLE="width: 326"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Payment Date[/TD]
[TD]Payment Amount[/TD]
[TD]If multiple entries at same value, how many? [/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]678.26[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]633.52[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]186.5[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]243.02[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2019[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]
If any of the above could be implemented it would greatly speed up my monthly bookings, so any help with this would be much appreciated.