Data transformation and Transpose in Excel

[sfdc1]

Hi There, I am just starting with advanced excel now and have following problem -

[TABLE="class: grid, width: 340"]
<tbody>[TR]
[TD="colspan: 4"]Primary set of data[/TD]
[/TR]
[TR]
[TD]Options[/TD]
[TD]Product[/TD]
[TD]Quantity[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Option 1[/TD]
[TD]Product 1[/TD]
[TD]1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Option 1[/TD]
[TD]Product 2[/TD]
[TD]1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Option 1[/TD]
[TD]Product 3[/TD]
[TD]1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Option 1[/TD]
[TD]Product 4[/TD]
[TD]1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Option 1[/TD]
[TD]Product 5[/TD]
[TD]1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Option 2[/TD]
[TD]Product 1[/TD]
[TD]2[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Option 2[/TD]
[TD]Product 2[/TD]
[TD]2[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Option 2[/TD]
[TD]Product 4[/TD]
[TD]2[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Option 2[/TD]
[TD]Product 5[/TD]
[TD]1[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Option 3[/TD]
[TD]Product 1[/TD]
[TD]3[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Option 3[/TD]
[TD]Product 2[/TD]
[TD]3[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Option 3[/TD]
[TD]Product 3[/TD]
[TD]3[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Option 3[/TD]
[TD]Product 4[/TD]
[TD]3[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]

I want to convert this data to -

[TABLE="class: grid, width: 530"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 3"]Quantity[/TD]
[TD="colspan: 3"]Total Price[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Option 1[/TD]
[TD]Option 2[/TD]
[TD]Option 3[/TD]
[TD]Option 1[/TD]
[TD]Option 2[/TD]
[TD]Option 3[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Product 3[/TD]
[TD]1[/TD]
[TD]NA[/TD]
[TD]3[/TD]
[TD]10[/TD]
[TD]NA[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Product 4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Product 5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]NA[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]NA[/TD]
[/TR]
</tbody>[/TABLE]


and then final transformation to -

[TABLE="class: grid, width: 350"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Option 1[/TD]
[TD="width: 64"]Option 2[/TD]
[TD="width: 64"]Option 3[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]1(10)[/TD]
[TD]2(20)[/TD]
[TD]3(30)[/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]1(10)[/TD]
[TD]2(20)[/TD]
[TD]3(30)[/TD]
[/TR]
[TR]
[TD]Product 3[/TD]
[TD]1(10)[/TD]
[TD]NA[/TD]
[TD]3(30)[/TD]
[/TR]
[TR]
[TD]Product 4[/TD]
[TD]1(10)[/TD]
[TD]2(20)[/TD]
[TD]3(30)[/TD]
[/TR]
[TR]
[TD]Product 5[/TD]
[TD]1(10)[/TD]
[TD]1(20)[/TD]
[TD]NA[/TD]
[/TR]
</tbody>[/TABLE]


OR to
[TABLE="class: grid, width: 350"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Option 1[/TD]
[TD="width: 64"]Option 2[/TD]
[TD="width: 64"]Option 3[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]10 (1)[/TD]
[TD]20 (2)[/TD]
[TD]30 (3)[/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]10 (1)[/TD]
[TD]20 (2)[/TD]
[TD]30 (3)[/TD]
[/TR]
[TR]
[TD]Product 3[/TD]
[TD]10 (1)[/TD]
[TD]NA[/TD]
[TD]30 (3)[/TD]
[/TR]
[TR]
[TD]Product 4[/TD]
[TD]10 (1)[/TD]
[TD]20 (2)[/TD]
[TD]30 (3)[/TD]
[/TR]
[TR]
[TD]Product 5[/TD]
[TD]10 (1)[/TD]
[TD]20 (1)[/TD]
[TD]NA[/TD]
[/TR]
</tbody>[/TABLE]

in Excel sheet. Thanks

 

[Mr_Olaf]

Hi sfdc1,
The easiest way is by Pivot table. Put "Product" field to row labels of pivot table, "Quantity" and "Price" to Values (Data field), and "Options" to column labels. This table you can also get with formulas with SUMPRODUCT function.
Create row and column labels for your result table (Product1,..., Product5, and Option 1,.., Option 3).
If your data start at cell A1, and your result table begins at A20 then put this formula in cell B21 and fill across all other cells in result table:
=SUMPRODUCT(--($A21=$B$2:$B$14),--(B$20=$A$2:$A$14),$C$2:$C$14) &" (" &
SUMPRODUCT(--($A21=$B$2:$B$14),--(B$20=$A$2:$A$14),$D$2:$D$14)&")"