Getting variance of Multiple linked cells

[Rafiuddin_333]

Hello all,

I am a newbie in Alteryx and seeking your help as to whether its possible to automate this task in Alteryx. Example:

Particulars	Amt (X)	Linked Codes	Cost (Y)	Variance (X)-(Y)
Product A	500	1025 - A	10	410
		1045 - A	15
		1035 - C	20
		1055 - B	30
Product B	300	1150 - A	10	380
		1250 - B	15
Product C	150	1350 - A	60

In the above example to be precise, I need the output in same column and format (Including Blanks). Now regarding the explanation as to how I got variance of 410 it includes Amount of A I.e. 500 and Sum of its linked codes I.e. (10+15+20+30) and Sum of 1250 - B I.e. 15 which gives 500-(10+15+20+30+15) = 410

Similarly in For the variance in Product B it comprises of Amount of B & C I.e. 300+150 subtracted with 1150 - A i.e. 10 & 1350 - A i.e. 60 = 300+150 -(10+60) = 380

The data that i have is like that and i need variance in the same column, Including Blanks too. Is it possible in Alteryx?, Can you help me out if u have any solution for it?

Thanks a lot for your effort and time!!

Regards,
Radic

[cjaneczko]

It looks like you need some sort of a lookup table. The logic of the pairs doesn't make any sense in the example. Not following how Product B and C get added but A is alone. Or how some of the codes under some products get added to codes in other products. 

[Raj · Accepted solution]

@Rafiuddin_333 
find the workflow attached
mark done if solved