Hello All,
Need your help for the following scenario
Shares | Net amount | Key | Difference |
1000 | 456 | A | 2 |
2000 | 4566 | A | |
3400 | 786800 | A | |
24500 | 8700 | B | 3 |
2000 | 767800 | B | |
5600 | 7869800 | B | |
6700 | 7867800 | B | |
7800 | 8700 | B
|
So here i have to start subtracting the difference Amount to all net amount such that it start assigning 0.01 from first net amount then the next group by key till whole difference is allocated to net amount.
I have already this in mind that i first calculate
Difference/0.01*no of rows per key, and then start assigning that amount.
But how to do it that suppose there are 6 rows only and difference amount is say 0.02, then it should only allocate to first two net amount.
Can anyone help ?
Solved! Go to Solution.
Hi @nazuk ,
Would you be able to show the calculation for key A? It is not clear to me how this works.
Best,
Fernando V.
Hi , so just take the below example
Shares | Net amount | Key | Difference | 1st round | Net amount | 2nd round | Net amount |
1000 | 456 | A | 0.05 | 456+0.01 | 456.01 | 456.01+0.1 | 456.02 |
2000 | 4566 | A | 4566+0.01 | 4566.01 | 4566.01+0.1 | 4566.02 | |
3400 | 786800 | A | 786800+0.01 | 786800.01 |
So here the difference amount is 0.05, so i have to start allocating this difference amount untill it is allocated to all. So i always have to start allocating to the small holding first 0.01 and later on, after one iteration we need to calculated how much is allocated and remaining need to do in next one
output should be:
Shares | Net amount | Key | Difference |
1000 | 456.02 | A | 0.05 |
2000 | 4566.02 | A | |
3400 | 786800.01 | A |
Hi @nazuk ,
I didn't follow your step by step to create the logic because it would make the workflow more complex since we would need to create an iterative macro.. so I adapted the logic to make it easier.
Best,
Fernando Vizcaino