Multi Row Subtraction/Addition

[hemant86]

HI Team,

I have the below requirement.

Input:

row no.	EMP ID	Code	A	B
1	123	L	19
2	123	L	30
3	123	L	32
4	123	L	37
5	123	D	14
6	123	D	24
7	123	D	37

The Column B calculation should be done grouped by EMP ID & Code. Wondering if this can be achieved through multirow tool. Appreciate any help around this.

Required Output:

row no.	EMP ID	Code	A	B
1	123	L	19	19		B1=A1
2	123	L	30	11		B2=A2-A1
3	123	L	32	2		B3=A3-(A2+A1)
4	123	L	37	5		B4=A4-(A3+A2+A1)
5	123	D	14	14		B6=A6
6	123	D	24	10		B7=A7-A6
7	123	D	37	13		B8=A8-(A7+A6)

[Raj · Accepted solution]

@hemant86 
find the workflow

attached

mark done if solved.

[Qiu]

@hemant86 
Your formula is not consistent with your value of B.

Maybe?

B3=A3-(B2+B1)

[hemant86]

HI @Qiu Apologies for the typo. Thanks for highlighting . Please find the updated output below.

	EMP ID	Code	A	B
1	123	L	19	19		B1=A1
2	123	L	30	11		B2=A2-A1
3	123	L	32	2		B3=A3-(B2+B1)
4	123	L	37	5		B4=A4-(B3+B2+B1)
5	123	D	14	14		B5=A5
6	123	D	24	10		B6=A6-A5
7	123	D	37	13		B7=A7-(B6+B5)

[Raj]

@hemant86 
you can refer the solution attached
i guess that solves the problem.

[hemant86]

Thanks @Raj I was under an impression that the multi row formula you used will minus only the immediate previous row. Appreciate if you could share some insight on how it is taking sum of all previous rows and then doing a minus. Thanks again for the solution. 

[Raj]

@hemant86 Just used the basic math's to fulfill your requirement but 
to answer your question 
we can change this and this should work for multiple rows as well

hope this helps.

[hemant86]

@Raj If I'm getting your point correct, the number of rows for each employee in my data is not fixed. Keeps varying. 

[Raj]

@hemant86 
no this is not restricted to  number of rows.
this is dynamic and will work for any number of rows.