Challenged by special sort order....

[jhren]

Hard to explain, but I need a sorted list of unique values, based on an adjacent column of the same unique values in a haphazard order. The start value which is off by itself determines the start of the sort order.

It is actually a two column representation of multi-level distribution routing. I need the hub end highest and final destination lowest in the sorted list, with in-betweens "ranked" accordingly.

Any ideas on how to accomplish this?

[Special-K]

Just add a Helper column designating what "level" each element is on and sort by that then the element value

[jhren]

Hey there, SK...

Thanks for your consideration. However, the sample posted is just a small sample to explain the scenario. The actual data set is substantially larger. To determine level manually is beyond mind boggling... as just the small sample data set is every bit of that (at least it is for mine ).

Need a way to automate the task...

[newdoverman]

What criteria determine the levels as shown in the diagram? There doesn't seem to be any logic to it.

[jhren]

It all starts at the "hub", which is A in the diagram. The next level is determined by the To's that are From A (3 and 5). The next level is determined by the To's From A via 3 and 5 (2 and 9 via 3, and 1 and 7 via 5)... and so on.

As an analogy, say you had five trucks that leave the hub. Three take Route 3 and two take Route 5. As the three that took Route 3 get further down the road, one takes Route 9 to complete the route while two take Route 2 then split off to Routes 4 and 10 to their final destination. Getting back to the two that left the hub via Route 5, they split off and take Routes 1 and 7, then respectively switch over to Routes 8 and 6 to their final destination.

PS: I'm only using A and numbers 1 thru 10 as an example. In the actual file they can be any alphanumeric designation.

[jhren]

Well, I managed to get the sample file sorted. The method is quite convoluted, so I'm not going to mark the thread solved... hoping someone can suggest a better solution!