Q: Pairing groups which will not repeat

Hi Jay,

Here is one solution for 5 students

Formula in C2 (and Fill Right and Fill Down)

=IF($B2≠C$1,$B2&", "&C$1,"No match")

For "not equal to" type <> and Numbers will change it to ≠

The IF function is so that a student is not paired with themself, and inserts No match .

Conditional Highlighting

Regards,

Ian.

Hi Jay,

Ian's solution will show you all possible pairings. If you are wanting formulas that will give you 20 different groups of those pairs then Numbers is not, perhaps, the best tool. This is a great a great puzzle and I will be thinking about this. I don't think there will be a "simple" or straightforward solution but it will be fun to try.

quinn

Cross-posted in Pages for Mac community with a non-spreadsheet (Python) combinations (nCr) approach.

I think this is just another form of the "round robin tournament" problem. Give each student a Number and here is one way to calculate pairings in a Numbers table:

Enter the number of students in B1.  List the weeks down column A and pairs across row 2, every other cell without merging cells (turn of wrap text).

The formulas are:

D1: =IF(ISODD(B1),B1+1,"")

E1: =IF(ISODD(B1),"is a Bye","")

B3: =1

C3: =MAX(3,CEILING($B$1,2))

D3: =B3+1

E3: =C3−1

F3: =D3+1

G3: =E3−1

H3: =F3+1

I3: =G3−1

J3: =H3+1

K3: =I3−1

L3: =J3+1

M3: =K3−1

N3: =L3+1

O3: =M3−1

P3: =N3+1

Q3: =O3−1

R3: =P3+1

S3: =Q3−1

T3: =R3+1

U3: =S3−1

B4: =1

C4: =MOD(C3−3,MAX(3,CEILING($B$1,2))−1)+2

Fill formula in C4 right and down through the rest of the table.

Set Conditional Highlighting to identify the "byes" where a student will be the "odd person out" for that week.

Rows and columns can be added to accommodate larger numbers of students, or hidden to accommodate smaller numbers.

To list the students by name set up a lookup table with number and student name on each row and use VLOOKUP in another table to replace the numbers with names.

In my testing this works fine in a Pages table too.

SG