Help to Create a Formula

[Sagduel]

Hi,

Presently i am working on data that seems like this which relates to payment
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Payment date[/TD]
[TD]Document no.[/TD]
[TD]Amount[/TD]
[TD]Interest [/TD]
[TD]Due date[/TD]
[/TR]
[TR]
[TD]12-Apr-17[/TD]
[TD]120401[/TD]
[TD]10000[/TD]
[TD]1000[/TD]
[TD]1-Apr-2017[/TD]
[/TR]
[TR]
[TD]10-May-17[/TD]
[TD]120402[/TD]
[TD]20000[/TD]
[TD]2000[/TD]
[TD]1-Apr-2017[/TD]
[/TR]
[TR]
[TD]30-Jun-17[/TD]
[TD]120403[/TD]
[TD]10000[/TD]
[TD]1000[/TD]
[TD]30-May-17[/TD]
[/TR]
</tbody>[/TABLE]








Now we have linked this payments based on the FIFO basis for the the invoice where the following are the detailed present in invoice.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Invoice date[/TD]
[TD]Invoice Number[/TD]
[TD]Amount[/TD]
[TD]Due Date[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1-Mar-2017[/TD]
[TD]120301[/TD]
[TD]30000[/TD]
[TD]1-Apr-2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1-May-2017[/TD]
[TD]120501[/TD]
[TD]15000[/TD]
[TD]30-May-2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]






Data which is Have Basically i have the details, that is involves debit and credit in single sheet and if we total up debit and credit the sum=0. I.e i have all the invoice and payments matched data.

What i require is For which invoice the payment has been matched and how many times the payment is adjusted to the invoice and how much interest is charged against which invoice.

The due date is the only constant figure here as there is no other reference.,

Challenges faced while doing this excercise.
1. Multiple invoice contains same due dates.
2. Payments are made in multiples for single invoice.
3. Payments are adjusted to multiple invoices.

It's urgent Kindly provide any idea.

Presently we are giving reference number manually for all the payments and invoice. Where we have 60000 above line items in one month as it will take more than 6 months of manual reference for doing one year matching.

 

[mole999]

you have document number and invoice number, are they supposed to be the same item. A Pivot table might be the best way to view without changing what you are starting from

 

[Sagduel]

Sorry, Document number and invoice number are not same.
As the software creates new document number for each of the entries passed in the software and hence the invoice number is not same as document.

We dont have access to software also. Only what we have is excel sheet with due date as common reference.

 

[Sagduel]

I challenge if any one will solve this. Will get a Party from my side