Adjusting Negative Amounts

Hi,

I have a requirement in whch I need to traverse through all the rows and depending on condtion, I would like to settle amount.

For example, see the input file-

Sector Amount
C71 -10
C72 -20
C73 70
C74 90

So
If Sector =C71 and Amount < 0 Then Amount becomes zero BUT I have to settle this amount in Sector C73, meaning C73 becomes (70-10) = 60
Similarly
If Sector =C72 and Amount < 0 Then Amount becomes zero BUT I have to settle this amount in Sector C74, meaning C74 becomes (90-20) = 70

So the output file should look this this -

Sector Amount
C71 0
C72 0
C73 60
C74 70

The idea is to settle the negatives of C71 in C73 and C72 in C74 so that the total amount remains same(130).

Please find attached input file.

Thanks

Areeba

Input_File.xlsx

Output

Accepted answers

atcodedog05

Hi @areeba

Here is a workflow for the task.

Output:

Workflow:

I have added annotations to the tools and added comments where the keys needs to be changed so that you can change for other cases

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

sector.yxzp

sector.yxmd

All comments

atcodedog05

Here is a workflow for the task.

Input:

Output:

Workflow:

Hope this helps 🙂

PS: the sectors in your problem statement posted and sector in your data is mismatching.

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

amount.yxmd

areeba

@atcodedog05

Hi,

Thank you for reply. I guess I attached the wrong file but you got that right.. Thanks..

I follow your solution, but in our case it is not necessary that C71 will be two rows up than C73, same goes for C74 and C72 combination.

These sectors may appear at different random lines in actual file. Can you suggest something to make this dynamic, meaning the solution should work irrespective of the row number these sector....

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703