Create random doubles matches for two tennis groups in a spreadsheet

Other Questions Google docs
1

I’m working on a spreadsheet for a tennis tournament. I’ve got two groups of players, A and B, with 8 players each. I want to set up random doubles matches following these rules:

  1. Players from Group A pair up with another Group A player (not themselves)
  2. Players from Group B do the same
  3. An A pair plays against a B pair
  4. We need 4 games total, using all 16 players

I’ve tried using formulas like INDEX and RANDBETWEEN to pick random players, and FILTER to avoid duplicates. But I keep running into circular reference issues.

Here’s what I’m aiming for:

| Group A1 | Group A2 | Group B1 | Group B2 |
|----------|----------|----------|----------|
| Player 1 | Player 5 | Player 9 | Player 13|
| Player 2 | Player 6 | Player 10| Player 14|
| Player 3 | Player 7 | Player 11| Player 15|
| Player 4 | Player 8 | Player 12| Player 16|

Any ideas on how to make this work without circular references?

2

I’ve run into this exact problem organizing doubles matches for my office tennis league. What worked for me was using a combination of VLOOKUP and RANDBETWEEN functions. Here’s the trick:

Create a separate ‘randomizer’ sheet with player names and random numbers. Then use VLOOKUP to pull these randomized players into your main sheet.

Something like:
=VLOOKUP(RANDBETWEEN(1,8),Randomizer!A1:B8,2,FALSE)

This avoids circular references because the random numbers are generated separately. You might need to tweak it a bit, but this approach has been rock-solid for our tournaments.

One caveat: you’ll need to manually refresh the sheet to get new matchups. But it’s a small price to pay for headache-free randomization.

3

hey, i’ve dealt with this before. try using array formulas instead. something like:

=ARRAYFORMULA(INDIRECT(“A”&SEQUENCE(4,2,1,1)&“:”&SEQUENCE(4,2,5,1)))

for group A, and similar for B. then just match em up. it’s a bit tricky to wrap ur head around at first, but once u get it, it works like a charm. no circular refs either!

4

I’ve tackled a similar challenge for our local tennis club. Instead of relying solely on formulas, I found using a combination of RAND() and SORT() functions more effective. Here’s a method that worked well:

  1. Assign random numbers to each player using RAND().
  2. SORT both groups separately based on these random numbers.
  3. Pair the first with the fifth, second with the sixth, and so on in each group.
  4. Match A pairs against B pairs in order.

This approach eliminates circular references and ensures fair, random pairings. You might need to refresh the sheet to generate new matchups. If you’re still struggling, consider using Google Sheets’ script editor to create a custom function. It offers more flexibility for complex randomization tasks like this.