Calculation of a value into multiple row

[JaesunK]

Hi,

I would like to allocate a value in different rows into multiple rows. For example, the below tables show what I would like to achieve. 

Source table

Item	Amount
a	1
b	1
c	1
a+b	2 (to be allocated into a ,b)
a+b+c	3 (to be allocated into a, b, c)

Resultant table 

Item	Amount
a	3 (1+1+1)
b	3 (1+1+1)
c	2 (1+1)

Could anyone please advise which tool(s) & formulas that I can use to make this happen? 

Kind regards,

Jaesun

[Felipe_Ribeir0]

Hi @JaesunK 

One way of doing this (updated)*:

Input:

Output:

[JaesunK]

Hi @Felipe_Ribeir0 

Thanks for your reply and the proposed solution.

I noticed that this proposed solution might only work for the sample I provided.

I have attached another sample with the actual numbers as attached (it has input, working, and outcome tab separately)

Could you please help me with this sample? 

Kind regards,

Jaesun

[Felipe_Ribeir0 · Accepted solution]

Hi @JaesunK 

Although your input (Book1.xlsx) seems similar to the first sample, the solution is very different (for example, the first sample had + separating items, this new input uses /). Anyway, here is the solution adapted to the second input.

[JaesunK]

This is fantastic!

Thanks for your help @Felipe_Ribeir0

Kind regards,

Jaesun

[JaesunK]

Hi @Felipe_Ribeir0

Just one quick additional question please.

Now I would like to allocate "Distribution Alpha/Beta/Gamma" into a certain ratio like 50% Alpha, 40% beta and 10% Gamma, would you be able to advise how I can reflect this change please?

Kind regards,

Jaesun

[Felipe_Ribeir0 · Accepted solution]

@JaesunK you can use formulas like these ones to do this: