Compile different column data

[sanket_sk]

Hi All,

We are collecting spare sale information using Microsoft, data is getting accumulated as Spare code 1 – Spare code 1 Qty , Spare code 2 – Spare code 2 Qty , Spare code 3 – Spare code 3 Qty , Spare code 4 – Spare code 4 Qty .

Now we want to compile the file as ASC name , spares list with Qty but since data is stored in separate columns (in some cases only 2 spare codes are filled instead of 6) , it is very difficult to compile the file.

Do we have any trick , Formula which will help us in compiling the data

Data format

[TABLE="width: 1396"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Name of the ASC[/TD]
[TD]Spare Code 1[/TD]
[TD]Spare Code 1 Quantity[/TD]
[TD]Spare Code 2[/TD]
[TD]Spare Code 2 Quantity[/TD]
[TD]Spare Code 3[/TD]
[TD]Spare Code 3 Quantity[/TD]
[TD]Spare Code 4[/TD]
[TD]Spare Code 4 Quantity[/TD]
[TD]Spare Code 5[/TD]
[TD]Spare Code 5 Quantity[/TD]
[TD]Spare Code 6[/TD]
[TD]Spare Code 6 Quantity[/TD]
[/TR]
[TR]
[TD="align: center"]Tatya Electronics[/TD]
[TD="align: center"]311520953681[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]996510069596[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]996510069593[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]311520953681[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Need in this format
[TABLE="width: 569"]
<colgroup><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Spare Code[/TD]
[TD="align: right"]25-05-19[/TD]
[TD="align: right"]26-05-19[/TD]
[TD="align: right"]24-05-19[/TD]
[TD="align: right"]27-05-19[/TD]
[/TR]
[TR]
[TD]Narendra Enterprises[/TD]
[TD="align: center"]311520953681[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"]996510069596[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"]996510069593[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
</tbody>[/TABLE]


Sanket