Random Hi-Low Horshoe Teams

[Keith16900]

Hello,
I am an intermediate user with no VB coding experience.

I run a Tournament twice a year with a different number of players each time. The Format is simple, I List all players present in chronological order based on their ringer percent hi to low and asign a number ex (1-20). I then divide the number of players in half so 1-10 are good players and 11-20 are not as good.

I randomly generate numbers using randlotto 1-20 and seperateley randlotto 21-40 which i then write down on a chart from each list. The objective is to have only good players partnered with bad partners and not putting 2 good players together.
Example: 1,9,7,3,4,2,8,5,6,10 good players
Example: 12,14,18,20,15,19,11,16,13,17 bad players

Example:Then written 1&12 vs. 9&14 pit 1, 7&18 vs 3&20 pit 2 and so on.
I do this for 6 rounds of all players. Each player gets to play 6 games.

I have been trying for years to have excel populate all match-ups for all 6 rounds without duplicates in any round. I run into this duplicate pairings alot and duplicate players playing each other, I get many complaints from putting 2 guys together that have already been partnered and any given players playing each other in some way.

I would like to just enter the number of people who show up in a cell and have an array populate 6 -2 column groups with no duplicates. Each 2 column group would be randomly generated ,example: column 1 would be 1-10(randomized) and column 2 would be 11-20(randomized), column 3 would be 1-10 again and so on. so it reads like this;

I hope this is not confusing I appriciate any help,
Thanks,
Keith

 

[West Man]

In your example do players 1 & 12 remain partners for the entire 6 rounds?

 

[shg]

Welcome to the board.

How about this:

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

Bad​

Rand()​

Good​

Round 1​

Round 2​

Round 3​

Round 4​

Round 5​

Round 6​

2​

11​

0.26929​

1​

16​

14​

11​

17​

18​

15​

A2:A11: Input

3​

12​

0.5499​

2​

14​

11​

17​

18​

15​

12​

B2 and down: =RAND()

4​

13​

0.97837​

3​

11​

17​

18​

15​

12​

19​

D2:D11: Input

5​

14​

0.25335​

4​

17​

18​

15​

12​

19​

13​

E2 and down: =INDEX($A$2:$A$11, RANK(B2, $B$2:$B$11))

6​

15​

0.11777​

5​

18​

15​

12​

19​

13​

20​

F2 and down and across: =INDEX(E$2:E$11, MOD($D2, 10) + 1)

7​

16​

0.51105​

6​

15​

12​

19​

13​

20​

16​

8​

17​

0.76597​

7​

12​

19​

13​

20​

16​

14​

9​

18​

0.11128​

8​

19​

13​

20​

16​

14​

11​

10​

19​

0.56681​

9​

13​

20​

16​

14​

11​

17​

11​

20​

0.04232​

10​

20​

16​

14​

11​

17​

18​

Columns A & B randomize the order of appearance of the bad players in column E (the first round).

Columns F:J rotate the bad players in each round.

You could drag column J right to have up to 10 rounds.

 

[Keith16900]

Each of the 6 rounds new hi-lo partners are matched up

 

[shg]

Indeed they are.

 

[Keith16900]

Shg,
Thank you, this works great, with just a couple of issues with what I am trying to do. The first thing is I would need to randomly place the good players on the pits also(so player 1 is not up first ar on pit 1 everytime), so i could not list them 1-10, can these be randomized and the table still work? Also would it be possible to set this up to populate by entering just one number which is the number of players who show up? Example: in cell A1 enter 20 (players) and then the table is populated? The reason for this is having people not showing up or someone showing up just after i create the table which forces me to re-do it.
I used the concenate function to pull this data which would be the final product i need as an example

	Round 1	Round 2	Round 3	Round 4	Round 5	Round 6
PIT 1	1 & 15	1 & 12	1 & 13	1 & 16	1 & 18	1 & 19
	2 & 12	2 & 13	2 & 16	2 & 18	2 & 19	2 & 14
PIT 2	3 & 13	3 & 16	3 & 18	3 & 19	3 & 14	3 & 11
	4 & 16	4 & 18	4 & 19	4 & 14	4 & 11	4 & 20
PIT 3	5 & 18	5 & 19	5 & 14	5 & 11	5 & 20	5 & 17
	6 & 19	6 & 14	6 & 11	6 & 20	6 & 17	6 & 15
PIT 4	7 & 14	7 & 11	7 & 20	7 & 17	7 & 15	7 & 12
	8 & 11	8 & 20	8 & 17	8 & 15	8 & 12	8 & 13
First	9 & 20	9 & 17	9 & 15	9 & 12	9 & 13	9 & 16
available	10 & 17	10 & 15	10 & 12	10 & 13	10 & 16	10 & 18

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

Thanks again for your help