Rearranging Data
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Here's another depiction to show the challenge:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for your help with this.
