Data transformation and Transpose

[sfdc1]

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

Live data in case you want to download and try it - https://docs.google.com/spreadsheets...J2k/edit#gid=0

Primary set of data
Options Product Quantity Price
Option 1 Product 1 1 10
Option 1 Product 2 1 10
Option 1 Product 3 1 10
Option 1 Product 4 1 10
Option 1 Product 5 1 10
Option 2 Product 1 2 20
Option 2 Product 2 2 20
Option 2 Product 4 2 20
Option 2 Product 5 1 20
Option 3 Product 1 3 30
Option 3 Product 2 3 30
Option 3 Product 3 3 30
Option 3 Product 4 3 30

I want to convert this data to -

Quantity Total Price
Option 1 Option 2 Option 3 Option 1 Option 2 Option 3
Product 1 1 2 3 10 20 30
Product 2 1 2 3 10 20 30
Product 3 1 NA 3 10 NA 30
Product 4 1 2 3 10 20 30
Product 5 1 1 NA 10 20 NA


and then final transformation to -

Option 1 Option 2 Option 3
Product 1 1(10) 2(20) 3(30)
Product 2 1(10) 2(20) 3(30)
Product 3 1(10) NA 3(30)
Product 4 1(10) 2(20) 3(30)
Product 5 1(10) 1(20) NA


OR to

Option 1 Option 2 Option 3
Product 1 10 (1) 20 (2) 30 (3)
Product 2 10 (1) 20 (2) 30 (3)
Product 3 10 (1) NA 30 (3)
Product 4 10 (1) 20 (2) 30 (3)
Product 5 10 (1) 20 (1) NA

in Excel sheet. Thanks

[AlKey]

Please see attached file with formulas