Random Driver Chart, Never Same Vehicle or Passenger

Hey All,

So we have an event coming up that requires us to build a completely random chart of drivers, vehicles and legs. We have been struggling with how we should do this and honestly don't even know where to start.

I've attached a file that should give an idea of what we need to do but there are three random variables.

Variable 1: Driver's and Passenger should rotate every leg.
Example: if you were a Driver in Leg 1, then you should be a Passenger in Leg 2. If you were A Passenger in Leg 1, then you would drive in Leg 2... So on and so forth.

Variable 2: The same two people should never end up a pair again.
Example: If person number 1 and 7 were together in leg 1, then they should never be together again.

Variable 3: You should never been in the same vehicle again.
Example: If you were either and Driver or Passenger in Car 1, then you should never been a Driver or Passenger in Car 1 again.


I assume this is possible in excel but like I said, not sure where to start so any help is useful.

Hi Toyo,

In variable 2, did you state it correctly? Should it be "The same two people should never be in the same car again"?

You assume the problem is possible with all these rules. Do you know for sure it is possible? I've seen too many problems where the answer is impossible and people try to solve it, thinking it is.

I will reiterate MarvinP's comment -- Is it possible that the problem is overconstrained to where it is impossible to meet all criteria.

This is not the kind of thing that I do, so I don't know if this will help, but my first thought was that this kind of sounds like a "Round robin" tournament, for which I know there are well established algorithms.
Wikipedia: https://en.wikipedia.org/wiki/Round-robin_tournament
Tutorial specific to Excel: https://www.get-digital-help.com/201...in-tournament/

The basic RR tournament could be used to rotate through the pairings.
You could extend it to include "Home/Away" or "Black/White" criteria to account for driver/passenger.
Then extend again to include vehicle.
Then eliminate all duplicate pairings.

If someone else comes up with a better approach, then go for it. As someone who would not really know where to begin to someone who says he/she does not know where to begin, my first thought for a starting place is a round robin tournament generator and see what evolves from there.

Hey Toyo,

I tried a few methods and seem to have stumbled onto an answer. Find the attached with each person number in a cell. Check my answer to see if it is what you want. I don't have any formulas as I simply did it trying to use your rules.

Car Driver Passenger Pairing No Dups.xlsx

I have this funny rule about problem solving, that worked in this case. If you have a problem you first need to guess an answer. If you can't guess then start trying to make the problem simpler to discover a formula. I didn't need step 2 in your problem. I was able to simply guess a correct answer. Solved?

Hey MarvinP,

That work's perfectly and is so simple, thank you!