Rearranging Data

[TJ2]

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. 

[DavidP]

If the 1st transaction is 100%, this will work, if not, we'll have to rethink.

[TJ2]

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...

[TJ2]

Here's another depiction to show the challenge:

[DavidP]

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.

[TJ2]

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

[DavidP]

Have a look if the following is the type of thing you're looking for.

I've added a new column called product and added a second product with a different allocation and a different cost figure.

This should therefore be able to support multiple products and their allocation.

[TJ2]

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?

[DavidP · Accepted solution]

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.

[TJ2]

Thanks for your help with this.