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

Name of the ASC

Spare Code 1

Spare Code 1 Quantity

Spare Code 2

Spare Code 2 Quantity

Spare Code 3

Spare Code 3 Quantity

Spare Code 4

Spare Code 4 Quantity

Spare Code 5

Spare Code 5 Quantity

Spare Code 6

Spare Code 6 Quantity

Tatya Electronics

311520953681

1

996510069596

1

996510069593

1

311520953681

2

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

Need in this format

Name	Spare Code	25-05-19	26-05-19	24-05-19	27-05-19
Narendra Enterprises	311520953681	1
	996510069596				1
	996510069593		1

<colgroup><col><col><col><col span="3"></colgroup><tbody>
</tbody>

Sanket