0

I am in the process of generating random names using =Rand(), grouping into 4 using =rank() and filtering. Using that group of 4 I create 2 teams of 2 which creates a random pairing doubles match. I then do this for several games and matches. My problem occurs due to keeping a track of who has played with each other before.

For example week 1 Player A & Player B and Player C & D. The following week Player A & Player D vs Player C & B this is an example using only 4 players but we have upto 20 members could potentially come and play. Also not everyone comes every week.

Any ideas of how I could ensure the players weren't partnered (exist in the list) with previous pairings but keeping it random. As I have generated it several times but it always has gaps of partners who have played before.

Happy to share my google sheet document - https://docs.google.com/spreadsheets/d/1iIO4yvMixfPP6uI7RZeoQfB5XCWq6zQOQy1E0ICTmQE/edit?usp=sharing

Thanks, J

JvdV
  • 70,606
  • 8
  • 39
  • 70
Jess Sheasby
  • 1
  • 2
  • 1
    Maybe create a list of all possible pairings to start with. Then apply `RAND()` to the list of pairings. Then track which pairings have occurred. Pairings with an absent member get filtered out before match assignments. – bugdrown Jan 25 '22 at 02:22
  • Similar to @bugdrown 's suggestion, but I'd say generate a list of all possible pairs, and from that generate a list of all possible matches. Then shuffle that match list. Then starting at the top of that list, each week play matches from list. Each week skip any match where a player is absent. Mark off each played match – chris neilsen Jan 25 '22 at 03:55
  • due to the nature of RAND this wont be doable as you imagine it. see: https://stackoverflow.com/a/66201717/5632629 – player0 Jan 25 '22 at 04:29
  • Can anyone suggest an alternative way or an example of doing this? – Jess Sheasby Jan 30 '22 at 22:26
  • @bugdrown the only problem with doing the way suggested is that not everyone turns up each week. Which is why I am using Rand to ensure the pairings are randomly generated. – Jess Sheasby Feb 02 '22 at 00:40
  • @chrisneilsen the only problem with doing the way suggested is that not everyone turns up each week. Which is why I am using Rand to ensure the pairings are randomly generated. – Jess Sheasby Feb 02 '22 at 00:41
  • @Jess Sheasby you could apply rand to the pairings that have shown up. – bugdrown Feb 02 '22 at 01:41
  • @jess I addressed that in my suggestion : "Each week skip any match where a player is absent." – chris neilsen Feb 02 '22 at 05:08

0 Answers0