Need help to code a condition

[mandarhondule]

Hello,

I need to add comment based on below condition for all the account, can someone please help me with the Conditional code to make it possible, appreciate your help.

Example: 

For account 123 account if the amount is in negative and bucket is A1 and for the same account if B2 buckets Balance has amount greater then A1 Bucket amount, then need to make a comment in front of it as show in the O/P table

The condition we will be checking is for only A1 and B2 bucket, C3 doesn't need any action.

InPut:

Acc	Amount	Bucket	Balance
123	-52	A1	0
123	250	B2	200
123	300	C3	0
456	300	A1	700
456	-433	B2	0
456	100	C3	0
789	100	A1	10
789	200	B2	500
789	300	C3	500
1001	-167	A1	0
1001	250	B2	100
1001	300	C3	0

Output:

Acc	Amount	Bucket	Balance	Comment
123	-52	A1	0	B2 has sufficient balance
123	250	B2	200	B2 has sufficient balance
123	300	C3	0	NO action
456	300	A1	700	A1 has sufficient balance
456	-433	B2	0	A1 has sufficient balance
456	100	C3	0	NO action
789	100	A1	10	NO action
789	200	B2	500	NO action
789	300	C3	500	NO action
1001	-167	A1	0	B2 doesn't have sufficient balance
1001	250	B2	100	B2 doesn't have sufficient balance
1001	300	C3	0	NO action

[mst3k]

you want a multi-row formula tool to look at the value in the preceding row, and you want to group by account so that it restarts with each account number. i'm not totally clear on the rule you're trying to apply, but a multi-row formula tool would do it. you'll need a lot of IF statements to cover all your scenarios

[chukleswk · Accepted solution]

The following flow will give you the exact output that you want.

1. Because you take no action on C3 regardless of the other buckets, you can filter that out and create your comment for those automatically.
2. From there, you could filter out all of the negative Amounts, join those back on the positive amounts and check to see if the Balance in the positive Bucket has enough to cover the negative bucket and use a formula to create the desired comment.
3. Anything that doesn't match between the positive and negative buckets is assumed to have 2 positive Amounts and is assigned a comment of "NO action"