Conversion of Rows in to Coloums

[Sagduel]

Hi Experts,

The data which i have is explained like this

[TABLE="width: 0"]
<tbody>[TR]
[TD]Vendor
[/TD]
[TD]Type
[/TD]
[TD]D NO
[/TD]
[TD] Date
[/TD]
[TD]Due date
[/TD]
[TD]Product
[/TD]
[TD]Debit
[/TD]
[TD]Credit
[/TD]
[TD]Delay
[/TD]
[TD]Interest
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]Invoice
[/TD]
[TD]111111
[/TD]
[TD]14-02-18
[/TD]
[TD]16-03-18
[/TD]
[TD]ABC
[/TD]
[TD] 50,000.00
[/TD]
[TD] -
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]Invoice
[/TD]
[TD]111112
[/TD]
[TD]17-02-18
[/TD]
[TD]19-03-18
[/TD]
[TD]ANC
[/TD]
[TD] 40,000.00
[/TD]
[TD] -
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]Payment
[/TD]
[TD]222222
[/TD]
[TD]17-04-18
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD] -
[/TD]
[TD] 30,000.00
[/TD]
[TD]32.00
[/TD]
[TD]210.4
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]Payment
[/TD]
[TD]222223
[/TD]
[TD]17-04-18
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD] -
[/TD]
[TD] 10,000.00
[/TD]
[TD]32.00
[/TD]
[TD]70.1
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]Invoice
[/TD]
[TD]111113
[/TD]
[TD]15-03-18
[/TD]
[TD]14-04-18
[/TD]
[TD]ANC
[/TD]
[TD] 70,000.00
[/TD]
[TD] -
[/TD]
[TD]0.00
[/TD]
[TD]0.0
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]Payment
[/TD]
[TD]222224
[/TD]
[TD]15-04-18
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD] -
[/TD]
[TD] 10,000.00
[/TD]
[TD]0.00
[/TD]
[TD]0.0
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]Payment
[/TD]
[TD]222225
[/TD]
[TD]15-05-18
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD] -
[/TD]
[TD] 40,000.00
[/TD]
[TD]57.00
[/TD]
[TD]499.7
[/TD]
[/TR]
</tbody>[/TABLE]

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

For Example

[TABLE="width: 683"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Vendor[/TD]
[TD]Type[/TD]
[TD]D NO[/TD]
[TD] Date[/TD]
[TD]Due date[/TD]
[TD]Product[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Payment[/TD]
[TD]Credit[/TD]
[TD]Delay[/TD]
[TD]Interest[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Invoice[/TD]
[TD="align: right"]111111[/TD]
[TD="align: right"]14-02-18[/TD]
[TD="align: right"]16-03-18[/TD]
[TD]ABC[/TD]
[TD] 50,000.00[/TD]
[TD] - [/TD]
[TD="align: right"]17-04-18[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]210.4109589[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]17-04-18[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]70.1369863[/TD]
[/TR]
</tbody>[/TABLE]


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.