Need support on achieving one of the logic in Alteryx from excel data manipulation

[Phavaldar]

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.

[caltang · Accepted solution]

Like so?

Though I need to ask if it's possible for the amount to exist more than 2 rows per group, and if the amount exceeds 100,000 by default, how do you assign the group?

[Phavaldar]

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. 

[caltang]

@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?

[caltang · Accepted solution]

Thanks for more data. I think this should do it:

[Phavaldar]

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