Conversion of Rows in to Coloums

[Sagduel]

Hi Experts,

The data which i have is explained like this

Vendor	Type	D NO	Date	Due date	Product	Debit	Credit	Delay	Interest
ABC	Invoice	111111	14-02-18	16-03-18	ABC	50,000.00	-	0
ABC	Invoice	111112	17-02-18	19-03-18	ANC	40,000.00	-	0
ABC	Payment	222222	17-04-18			-	30,000.00	32.00	210.4
ABC	Payment	222223	17-04-18			-	10,000.00	32.00	70.1
ABC	Invoice	111113	15-03-18	14-04-18	ANC	70,000.00	-	0.00	0.0
ABC	Payment	222224	15-04-18			-	10,000.00	0.00	0.0
ABC	Payment	222225	15-05-18			-	40,000.00	57.00	499.7

<tbody>
</tbody>

The data which i require is all the payments made in bits should be allocated to the invoice amount and invoice number.

For Example

Vendor	Type	D NO	Date	Due date	Product	Debit	Credit	Payment	Credit	Delay	Interest
ABC	Invoice	111111	14-02-18	16-03-18	ABC	50,000.00	-	17-04-18	30000	32	210.4109589
								17-04-18	10000	32	70.1369863

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Is there a shortcut for this as there are lakhs of data to convert like this

Technically

all the multiple payments should come to next to the invoice and which ample space. I tried with Pivot table but was not use

 

[Special-K99]

VBA would be easiest, am no expert in VBA though.

How do you know payuments 222222 and 222223 are for Invoice 111111 as in your second example and not for invoice 111112?
Invoice 111111 is for 50,000 so there's still a payment due on that invoice isn't there (10,000.00) ?

 

[Sagduel]

The payments are allocated based on first in first out basis and hence the payment for 30000 and 10000 are allocated to invoice 111111.