Pivot table challenge

[Raesu]

Thanks for any help on this. I'm working with some survey data on market share and could use some advice. The pivot table is easy enough, but the challenge I'm facing is that I have some quantity data to blend in, but not sure how. Here's a simplified data set:


http://sdrv.ms/T1DQpL


The kicker here is that if the main product is A, that basically needs to be listed 3x since A quantity is 3 (column B). The same for B on line 4, needs to be multiplied by 5. 1 row = 1 panelist, so basically making extra rows for A to count it more would make sense but would be very time consuming. How can I get the pivot table to weight these products based on quantity?


Do I have to just add rows based on the quantity so the panelist count (displayed data in the pivot table) increases?

 

[Faseeh]

Hi Raesu,


I have downloaded the file but am unable to understand exact requirement.


I have understood that for col B, Main prod will be repeated the times the max value it has in col B. For A it is 3, for B it is 4 and for C it is 1??? Is that correct.


If you had consider B Qtty as criteria, A, B and C will be repated 2, 5 and 2 times each??? Is that correct??


Secondly what will be the shape of final output once we repeat it accordingly? What data / values will be diaplyed against each of them?? Will u creat a pivot from thus made table?


Regards,

Faseeh

 

[kaushik03]

Hi Raesu,


Like Faseeh, I am also not very clear about your end requirement.


But just to simplify, is your end requirement to get the count of the number of main products being sold( separately for A, B, C)? I mean for A it is 6, B=12 and C=7.


My guess might be completely wrong.


Please explain your requirement bit more clearly (as Faseeh asked the questions above).


Regards,

Kaushik