Iterative Calculation using an ordered stack of values

[alfonsoLdc]

Hello,

I have a somewhat complex problem that involves:

1. An event that triggers the breaking of relationships in an ordered table of two matched datasets. 
2. This creates gaps that that need to be filled. I think of this as buckets where the target amount is known.
3. The gaps are filled with values from lower in the table, i.e. a gap in row 2 will be filled in by values in row 3 or 4 or 5, etc. depending on certain criteria. In the mockup, B_Term must be between the range specified in A_Term and B_Amount >= A_Amount. Additionally, a row can only give up what has already been allocated to it previously ("Balance").
4. As amounts are drawn from below, the matches between the datasets will change. Example: There is a gap in Row 1 of $100 at A0001. $100 from Row 12 corresponds to A0012_B0003, representing the relationship (intersection) between the datasets. A0012_B0003 "gives" the $100 to A0001 which then becomes A0001_B0003 and A0012_B0003 becomes A0012 since there is a new gap created. 
5. If an amount from a lower row is unable to fully satisfy the target amount in a higher one, then another lower row with enough balance to give is also used. This is denoted in Shift1 and Shift2.
6. The calculation must stop once there are no more rows from which to draw amounts from even if gaps remain. 

Basically each row represents a bucket with a target amount that must be filled and it can take amounts only from rows beneath provided they match certain criteria which in turn creates new gaps that must be filled from even lower rows.

This is somewhat difficult to explain without showing so I mocked up what is essentially the process in Excel. 

Thanks

[JarekSkudrzyk]

@alfonsoLdc hi, to get the data from rows beneath the current row you need to:
- reverse-order the data set (e.g. use recordID tool and sort descending)

- use multi-row formula to get the data from rows above from dataset with reversed order (i.e. rows beneath the dataset in not-reversed order).

- then you can reverse it again to return to the starting order
Let me know if this helps