Data transformation in Excel

[sfdc1]

Hi All, looking for help on converting data from one format to another -


If you want to see data in sheet is here -
https://docs.google.com/spreadsheet...8SLb2NNMRNqiXbqR8J2k/edit?pli=1#gid=555602833

Image - http://postimg.org/image/5gdyhs5gh/

Description -
1. Transpose primary data set and organize data so that options are shown as columns.
2. If there are multiple rows for same product and same option, need to sum it up and add as one row.
3. If products are not available for certain option then we need to update that cell as 'NA'
4. 'Primary set of data' may contain multiple rows, but it will have only 3 options at given point of time.
5. Above 3 scenrios are just for explanation of this problem, I want to develop one table which is dynamic
and changes are reflected as I make updates to data

 

[Drrellik]

try this in cell G6 on sheet 2 and drag it right 3 cells.

=SUMIFS($C$4:$C$11,$A$4:$A$11,G3,$B$4:$B$11,$F$4)/COUNTIFS($A$4:$A$11,G3,$B$4:$B$11,$F$4)&" ("&SUMIFS($D$4:$D$11,$A$4:$A$11,G3,$B$4:$B$11,F4)&")"

see if this helps.

~DR

 

[sfdc1]

@Drrellik Thanks for your reply, I tried and here what I got -

10 (1)

30 (0)

30 (0)

<tbody>
</tbody>

 

[Drrellik]

=SUMIFS($C$4:$C$11,$A$4:$A$11,G3,$B$4:$B$11,$F$4)/COUNTIFS($A$4:$A$11,G3,$B$4:$B$11,$F$4)&" ("&SUMIFS($D$4:$D$11,$A$4:$A$11,G3,$B$4:$B$11,$F$4)&")"


I did not lock the last F4 in the original try again.