Pairing Match Players limited by 5 matches

[pingpong1]

I have a system which Requires a group of 6 maximum which reveals 5 matches each. When the entry goes beyond 6 I still want to have them still play 5 matches. I got it to work with multiples of s which was easy, but to fing a repeating pattern to deal with the awkward entries between 7 & 11 I cant fathom. Thanks for your help.

[CAntosh]

Take a look at the attachment to see if it will do. I've entered 1 and 2 in the first matchup, then in D3 I have the array formula (confirm with Ctrl + Shift + Enter instead of Enter) below:

=MIN(IF(COUNTIF($D$2:$D2,$A$2:$A$13)+COUNTIF($E$2:$E2,$A$2:$A$13)=MIN(COUNTIF($D$2:$D2,$A$2:$A$13)+COUNTIF($E$2:$E2,$A$2:$A$13)),$A$2:$A$13))

and in E3 (also array-confirmed):

=MIN(IF(COUNTIF($D$2:$D2,$A$2:$A$13)+COUNTIF($E$2:$E2,$A$2:$A$13)+3*COUNTIFS($D$2:$D2,$D3,$E$2:$E2,$A$2:$A$13)+3*COUNTIFS($E$2:$E2,$D3,$D$2:$D2,$A$2:$A$13)+30*COUNTIF(D3,$A$2:$A$13)=MIN(COUNTIF($D$2:$D2,$A$2:$A$13)+COUNTIF($E$2:$E2,$A$2:$A$13)+3*COUNTIFS($D$2:$D2,$D3,$E$2:$E2,$A$2:$A$13)+3*COUNTIFS($E$2:$E2,$D3,$D$2:$D2,$A$2:$A$13)+30*COUNTIF(D3,$A$2:$A$13)),$A$2:$A$13))

Fill both formulas down through row 31 and it should return a set of matchups that look reasonably correct to me. For more or less than 12 players, add/subtract the numbers from column A and do a replace all on D:H. For instance, to work with 7 players instead of 12, do a 'replace all' on D:H, replacing $13 with $8 in the formulas (not values). There's probably a better way - I came to my solution via experimental tinkering - but take a look and see if it's good enough:

[pingpong1]

Does the formula work in rows of 12? Do I have to change the next 12 rows areas in the second formula. Im playing with it, Ill get back if Im stuck, thanks.

[CAntosh]

Does the formula work in rows of 12?

I'm not sure what you mean? The attachment I posted is set up for 12 players, but you can adjust it for more or fewer players using the 'replace all' method I described in post #2. I tested it with 7-12 players and it seemed to hold up reasonably well (no errors, even-looking matchups). You can apply it to any number of rows you like in columns D and E. I used the 30 that you had set up, but you can fill down beyond that for additional matchups. The formulas are not based on the number of available slots at all; they simply look at the previous matchups (however many there are) and select a pairing based in column D on fewest matches played thus far and for column E it uses fewest played and fewest against the proposed opponent. It is almost certainly not a flawless method, but the results look reasonable based on my understanding of the objective. Play with it a bit and let me know if you spot anything overly troubling.

[pingpong1]

It works perfectly. I simplified it from the workbook, but it needs to be integrated back in because I have row separations and things are in different columns. I will try and integrate it back. If Im stuck I'll attach the actual sheet if I may, thanks.

[CAntosh]

Glad I could help, good luck!

[pingpong1]

Hi. Ive tried several ways but cant get close to what you did integrating it back into my original. I copied one Tab in isolation to work on, but still couldnt work it. Pasting the Formulas removed the playing order. The only way I can see is to upload my book as there are links to other sheets which might be interfering? If you wouldnt mind tinkering a bit more for me I would be grateful. I assume if you get the first 2 tables working in WK 1 I can copy paste down in table pairs in that tab

[CAntosh]

Is there a new upload forthcoming? I'm not seeing anything...