Generating 2 rows on a different spreadsheet based on a value

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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top