Binning based on percentage

Column Value
Primary A 50
Primary B 100
Primary C 150
Secondary D 20
Secondary E 30
Secondary F 80

I have a column which consist of Primary and Secondary groups like the example above, what I would like to do is to group all Secondary groups into Primary group, so that the final column will only consist of all Primary groups. However, I need to divide some of the Secondary group's value by percentage among the Primary groups, for example: 50% of Secondary D goes to Primary A and B respectively, 1/3 of Secondary E goes to Primary A, B and C respectively and all of Secondary F goes into Primary C. The end results will look something like the table below.

Column Value
Primary A 70
Primary B 120
Primary C 240

Could someone give me some pointers on how I could achieve this?



(1) Answer