Solved

Excel Grid Matching Game

Posted on 2014-09-03
9
376 Views
Last Modified: 2014-09-04
My wife is being asked to come up with a "Prayer Partner" list, containing 12 women, who pray over the course of 11 weeks, each one partnered with the other only once.  Person 1 is the "Leader" of the group.  I have matched Person 1 to each person in the group once.  Row 1 has the prayer partner.  Column 3 starts the matching at Row 2 (first week):

ExcelPrayerPattern.PNG
I cannot figure out a way to complete the pattern.  Basically, if Person 1 is assigned to pray with person 2 in week 1, person 2 needs to be assigned to person 1 in week 1, and so forth.  In the above, I have person 1 assigned to each prayer partner in turn, and consequently, each person assigned to person 1 in the appropriate week.

Will someone please help me?

DrU
0
Comment
Question by:Justin Owens
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 40302715
Hello,

You have 12 people and 11 weeks. If they pray in pairs you have 6 pairs for each week.  Start with setting up prayer partners for person 1.

person 1 prays with person 2 first, and in the next weeks proceeds to take turns with each other person. That looks like this:
one
Notice how I filled in the first column as well. Underneath Person1 there is the Person number they are paired with in each week. In the first row you can see the numbers 2 and 1. Let's fill in the rest of the row with the other ten numbers.

shot2.png
3 pairs with 12 and 12 pairs with 3. Works out just fine.  

Let's fill in the same pattern for the remaining weeks, following the diagonal line of P1. Note that in some cases people would be paired with themselves, so we remove the value. These cells are highlighted in yellow.
threeFill in the start of the rows to complete the patterns and remove the numbers in cells where people are paired with themselves.
four
In week 2 we now see empty cells for P2 and P8, so we just pair these two. Similarly in week 4 we pair P3 with P9 and so on.
five
And that's it. Every person paired with every other person only once.

cheers, teylyn
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40302723
^ Beat me to it!  I was going to refer you to a 12-member round robin schedule (several generators on the Internet), but this explanation is really good.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 40302732
Except, I just spotted the error of my ways. P2 is paired with 8 in week 2 and in week 8. In fact, all the highlighted cells have duplicate pairings.

back to the drawing board, but too tired for mega Sudoku now.
0
 
LVL 31

Author Comment

by:Justin Owens
ID: 40302778
teylyn,

I actually came up with that, until I realized that every one ended up duplicating and missing 1 person.  For example, in the above, person 2 never prays with person 12, 3 with 11, etc.  (person 4 and 10 in row 7 actually works).  With that pattern, though, I could never get it to quite work... I spend several hours trying to figure something out, so I appreciate that you got so close so quickly.

DrU
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 84

Accepted Solution

by:
ozo earned 500 total points
ID: 40302798
  1   2   3   4   5   6   7   8   9  10  11  12
  2   1  12  11  10   9   8   7   6   5   4   3
  3   4   1   2  12  11  10   9   8   7   6   5
  4   6   5   1   3   2  12  11  10   9   8   7
  5   8   7   6   1   4   3   2  12  11  10   9
  6  10   9   8   7   1   5   4   3   2  12  11
  7  12  11  10   9   8   1   6   5   4   3   2
  8   3   2  12  11  10   9   1   7   6   5   4
  9   5   4   3   2  12  11  10   1   8   7   6
 10   7   6   5   4   3   2  12  11   1   9   8
 11   9   8   7   6   5   4   3   2  12   1  10
 12  11  10   9   8   7   6   5   4   3   2   1

Open in new window

0
 
LVL 84

Assisted Solution

by:ozo
ozo earned 500 total points
ID: 40303746
The pattern may be more obvious presented this way:
 1  3  4  5  6  7
 2 12 11 10  9  8

 1  4  5  6  7  8
 3  2 12 11 10  9

 1  5  6  7  8  9
 4  3  2 12 11 10

 1  6  7  8  9 10
 5  4  3  2 12 11

 1  7  8  9 10 11
 6  5  4  3  2 12

 1  8  9 10 11 12
 7  6  5  4  3  2

 1  9 10 11 12  2
 8  7  6  5  4  3

 1 10 11 12  2  3
 9  8  7  6  5  4

 1 11 12  2  3  4
10  9  8  7  6  5

 1 12  2  3  4  5
11 10  9  8  7  6

 1  2  3  4  5  6
12 11 10  9  8  7

Open in new window

0
 
LVL 31

Author Closing Comment

by:Justin Owens
ID: 40305070
ozo and teylyn,

Thank you both so much for the time and attention you put into this.  They have pushed back a week and will start next week.  One thing was asked.  It is actually two groups of 12 (24 total women), so as an interim solution i swapped 6 members of groups 1 and 2.  This caused 6 women to miss out on praying with 6 other women.

How difficult would it be to expand it to 24 x 24 instead of 12 x 12 and still not repeat?  If it is doable, I will ask a Related Question linked to this one.

DrU
0
 
LVL 84

Expert Comment

by:ozo
ID: 40305095
for 24, just extend the pattern
 1  3  4  5  6  7  8  9 10 11 12 13
 2 24 23 22 21 20 19 18 17 16 15 14

 1  4  5  6  7  8  9 10 11 12 13 14
 3  2 24 23 22 21 20 19 18 17 16 15

 1  5  6  7  8  9 10 11 12 13 14 15
 4  3  2 24 23 22 21 20 19 18 17 16

 1  6  7  8  9 10 11 12 13 14 15 16
 5  4  3  2 24 23 22 21 20 19 18 17

 1  7  8  9 10 11 12 13 14 15 16 17
 6  5  4  3  2 24 23 22 21 20 19 18

 1  8  9 10 11 12 13 14 15 16 17 18
 7  6  5  4  3  2 24 23 22 21 20 19

 1  9 10 11 12 13 14 15 16 17 18 19
 8  7  6  5  4  3  2 24 23 22 21 20

 1 10 11 12 13 14 15 16 17 18 19 20
 9  8  7  6  5  4  3  2 24 23 22 21

 1 11 12 13 14 15 16 17 18 19 20 21
10  9  8  7  6  5  4  3  2 24 23 22

 1 12 13 14 15 16 17 18 19 20 21 22
11 10  9  8  7  6  5  4  3  2 24 23

 1 13 14 15 16 17 18 19 20 21 22 23
12 11 10  9  8  7  6  5  4  3  2 24

 1 14 15 16 17 18 19 20 21 22 23 24
13 12 11 10  9  8  7  6  5  4  3  2

 1 15 16 17 18 19 20 21 22 23 24  2
14 13 12 11 10  9  8  7  6  5  4  3

 1 16 17 18 19 20 21 22 23 24  2  3
15 14 13 12 11 10  9  8  7  6  5  4

 1 17 18 19 20 21 22 23 24  2  3  4
16 15 14 13 12 11 10  9  8  7  6  5

 1 18 19 20 21 22 23 24  2  3  4  5
17 16 15 14 13 12 11 10  9  8  7  6

 1 19 20 21 22 23 24  2  3  4  5  6
18 17 16 15 14 13 12 11 10  9  8  7

 1 20 21 22 23 24  2  3  4  5  6  7
19 18 17 16 15 14 13 12 11 10  9  8

 1 21 22 23 24  2  3  4  5  6  7  8
20 19 18 17 16 15 14 13 12 11 10  9

 1 22 23 24  2  3  4  5  6  7  8  9
21 20 19 18 17 16 15 14 13 12 11 10

 1 23 24  2  3  4  5  6  7  8  9 10
22 21 20 19 18 17 16 15 14 13 12 11

 1 24  2  3  4  5  6  7  8  9 10 11
23 22 21 20 19 18 17 16 15 14 13 12

 1  2  3  4  5  6  7  8  9 10 11 12
24 23 22 21 20 19 18 17 16 15 14 13

Open in new window

0
 
LVL 84

Expert Comment

by:ozo
ID: 40305186
Maybe a little clearer written as:
 1 24 23 22 21 20 19 18 17 16 15 14
 2  3  4  5  6  7  8  9 10 11 12 13

 1  2 24 23 22 21 20 19 18 17 16 15
 3  4  5  6  7  8  9 10 11 12 13 14

 1  3  2 24 23 22 21 20 19 18 17 16
 4  5  6  7  8  9 10 11 12 13 14 15

 1  4  3  2 24 23 22 21 20 19 18 17
 5  6  7  8  9 10 11 12 13 14 15 16

 1  5  4  3  2 24 23 22 21 20 19 18
 6  7  8  9 10 11 12 13 14 15 16 17

 1  6  5  4  3  2 24 23 22 21 20 19
 7  8  9 10 11 12 13 14 15 16 17 18

 1  7  6  5  4  3  2 24 23 22 21 20
 8  9 10 11 12 13 14 15 16 17 18 19

 1  8  7  6  5  4  3  2 24 23 22 21
 9 10 11 12 13 14 15 16 17 18 19 20

 1  9  8  7  6  5  4  3  2 24 23 22
10 11 12 13 14 15 16 17 18 19 20 21

 1 10  9  8  7  6  5  4  3  2 24 23
11 12 13 14 15 16 17 18 19 20 21 22

 1 11 10  9  8  7  6  5  4  3  2 24
12 13 14 15 16 17 18 19 20 21 22 23

 1 12 11 10  9  8  7  6  5  4  3  2
13 14 15 16 17 18 19 20 21 22 23 24

 1 13 12 11 10  9  8  7  6  5  4  3
14 15 16 17 18 19 20 21 22 23 24  2

 1 14 13 12 11 10  9  8  7  6  5  4
15 16 17 18 19 20 21 22 23 24  2  3

 1 15 14 13 12 11 10  9  8  7  6  5
16 17 18 19 20 21 22 23 24  2  3  4

 1 16 15 14 13 12 11 10  9  8  7  6
17 18 19 20 21 22 23 24  2  3  4  5

 1 17 16 15 14 13 12 11 10  9  8  7
18 19 20 21 22 23 24  2  3  4  5  6

 1 18 17 16 15 14 13 12 11 10  9  8
19 20 21 22 23 24  2  3  4  5  6  7

 1 19 18 17 16 15 14 13 12 11 10  9
20 21 22 23 24  2  3  4  5  6  7  8

 1 20 19 18 17 16 15 14 13 12 11 10
21 22 23 24  2  3  4  5  6  7  8  9

 1 21 20 19 18 17 16 15 14 13 12 11
22 23 24  2  3  4  5  6  7  8  9 10

 1 22 21 20 19 18 17 16 15 14 13 12
23 24  2  3  4  5  6  7  8  9 10 11

 1 23 22 21 20 19 18 17 16 15 14 13
24  2  3  4  5  6  7  8  9 10 11 12

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now