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

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.