Below screenshot depicts what I'm trying to do. I have a list of Orders, it's settlements ,and the Allocation Percent and Area that are associated with them. I need the original data set to be transformed and look like the revised.
Somehow to determine for each order its last settlement. In this case, to associate the FIRST order with the FOURTH order and the associated Percent and Area.
Any help would be appreciated.
Solved! Go to Solution.
Thanks. Unfortunately, the actual data is not as orderly. So the first item is not 100%. The actual list has 1000+ items with similar patterns. One order is allocated to another order, then allocated to another order etc...
Here's another depiction to show the challenge:
If the sequence of transactions are not in the right order, you'll probably need an iterative macro. It would be helpful if you can share a small subset of representative data.
The tables below are representative of the data set.
Allocation table
Main Order Name | Settlement Order | Percent | Area |
FIRST | SECOND | 100% | A1 |
SECOND | THIRD | 50% | A1 |
SECOND | THIRD | 50% | A1 |
THIRD | FOURTH | 25% | A1 |
THIRD | FOURTH | 25% | A2 |
THIRD | FOURTH | 25% | A3 |
THIRD | FOURTH | 25% | A4 |
Cost table
Main Order | COST |
FIRST | 100 |
Thanks for the help so far. Is the key for this to work is that the cascading allocation logic has to be in sequential order. If the data is in random order, this will not work. So the source data needs to be sorted?
Essentially yes. FIRST and FOURTH are determined with the min and max value of the [Order] field in the Multi-Row formula at the start, which is calculated based on the order of Settlement Order.
If you can sort by Product and Settlement date, that might work. I mentioned earlier that you might need an iterative macro if the transactions are not in the right order, for instance, if all you have to go on is the pairs FIRST -> SECOND, SECOND -> THIRD, but they are not in the right order, you'll have to search through the list by product and find the 1st pair, then the 2nd pairs, and so on.
Thanks for your help with this.