Hi Community,
I have the below data in an Excel file also attached to this post.
SL.NO. | Company | Amount |
1 | Amazon | 40000 |
2 | Amazon | 30000 |
3 | Amazon | 30000 |
4 | Amazon | 70000 |
5 | Flipkart | 55000 |
6 | Flipkart | 30000 |
7 | Flipkart | 53000 |
8 | Flipkart | 20000 |
9 | Myntra | 11000 |
10 | Myntra | 77000 |
11 | Myntra | 20000 |
12 | Myntra | 50000 |
I want the desired output, as below:
SL.NO. | Company | Amount |
1 | Amazon | 74000 |
2 | Amazon | 98000 |
3 | Flipkart | 85000 |
4 | Flipkart | 73000 |
5 | Myntra | 88000 |
6 | Myntra | 70000 |
explanation, as below:-
If the total amount for a particular company is greater than $100,000, then it should split the payment into 2 lines for that respective company, and the split amount shouldn't exceed $100,000. DO NOT DIVIDE DIRECTLY WITH 2 AND SEPARATE.
The totals of the split amount should not be greater than 100,000 in 1 row and so on in other rows.
Note: In single-line items, the bank doesn't accept payment greater than $100,000. So we need to segregate the payments based on line items present in excel file and the split payment shouldn't exceed $100,000.
Solved! Go to Solution.
Yes, it's possible for the amount to exist more than 2 rows per group.
example as below for more understanding -
Attached is the excel file for your reference. But really thank you for the support on the issue which I am currently facing to achieve in one of my Alteryx automation.
@Phavaldar I will look at it tomorrow when I have time, it is getting late for me - but just curious, are you open to a macro solution as well?
Hi Calvin,
Thank you so much for your quick and effective help in solving my Alteryx issue! 😊 Your expertise and willingness to assist are truly appreciated. I'm grateful for the support provided by the Alteryx community. Thanks once again champ "Calvin Tang" 🏆
Praveen