Take detailed ledgers customer ID to General ledger.

[AnarB]

Hi, I'm working on this for a while and cant figure it out.
The problem is, I have general ledger transactions which has opposite account number with transaction numbers and as well as detailed ledger transaction which has customer ID but does not have transaction accounts opposite account number.
Tried joining them and got almost 90 percent of them joined successfully. But there are still some kind of transactions that are not able to join. Check the uploaded file please. I found that if transaction have more than 2 customer ID involved and related to VAT it became almost impossible for me to solve. How can i solve this problem? Thank you.

[Gaurav_Dhama_]

Your attached data involves human judgement, as your DL data is split into two GL Accounts, now on what basis have you split those amounts?
For example, A1 = 66 in DL, and in expected output, it is 6 for 3113-1 and 60 for 5101-1.

Also, the total of version2 is 100, while total everywhere else is 110, so on what basis you have got 90 and 10 as amounts for those two accounts.

If this task involves manual judgement, then it cannot be automated easily.

[AnarB]

The basis used for amount split is value added tax (VAT). It is about standard journal entry where VAT rate is 10 percent.
Dr AR for customer A 66
Cr VAT payable 6
Cr Revenue 60          /same journal entry goes for customer B journal entry/
And those journal entry is taken from 2 different ledgers GL and DL. When the journal entry has 1 customer in it it is fine no problem at all. But when journal entry involves 2 and more customer and plus VAT it became a little bit complicated.

[AnarB]

Version 2 was, I just structuring what kind of outputs can be there and one was version 2.  i may did not change the numbers sorry for my mistake. Here is the updated excel file.

[Gaurav_Dhama_ · Accepted solution]

This yields the same result as you expected. This is how I would do it, tweak it to your need and it should solve that problem.