Hi Bruce - I understand most of what you mean and might have a few ideas. I have a bit of confusion about your requirement #2, though. Can you elaborate? specifically on the below part:
Route 12 would display alt route 10 with a count of 1 and 16 with a count of 2
Sorry if I was not clear, let me try again. since route 12 is on trip 1 and trip 3, I would want to get the other trip numbers on the same route, and get a count of the number of times each one occurs, so on trip 1, besides, 12 there is route 10, 14 and 16, and on route 3 there is route 16, so for route 12, I would like to display 10 with a count of 1, 14, with a count of 1 and 16 with a count of 2 (since 16 is on 2 diff trips, or, in other words, I would like a count Distinct (tripNumber) for other routes sharing the same trip number
Does this help?
Here is an attempt that may work for you, or at least give you a start.
I used a custom SQL query to do a self-join and create combinations.
Then I did a calculation on the other side to filter out just the alternate routes,
and finally did a count. It seemed to give the right results.
Attached is the data file I used and the twbx.
The custom SQL could surely be improved upon,
and I'm not completely sure if the Index column I'd added to the data was necessary.
Thanks, I appreciate the fast response. I will check it out this weekend. (you know the old, "clear the alligators from the swamp!")