GEnerate Random Table From two Lists With Restrictions

[coliervile]

Good day to everyone-

I was asked by my supervisor to come up with a means to rotate our students through various positions of work and to rotate the students so that they do not work with the same students over and over again. There are a number of variables that I must contend with that makes it tough to devise a schedule such as this. Here are the variables:

Variable 1: Student’s work an 8 hour day and they get one hour off for lunch. The students work the various positions for a total of 7 hours a day and each position is worked for a period of one-hour, we refer to these one hour sessions as “Runs”. A student will get 7 Runs during the course of each day. The classes consist of 18 students.

Variable 2: Each student over a three hour period must rotate through each of three positions. The three positions are named Local, Ground, and Monitor.

Variable 3: Students cannot work the same position back to back. If a student worked the Local position they must work both of the other 2 positions next before working the Local position again. For example; a student could work Monitor, Ground, Local, Monitor, Ground, and Local or Local, Ground, Monitor, Local, Ground, and Monitor. They can’t work Local, Local, Monitor, Ground, Monitor, Ground, and Local.

Variable 4: Rotate the students as much as feasible so that they do not work with the same students over and over again.

Variable 5: Each student at the end of 13 days will have worked each of the three positions 30 times for a total of 90. On the 13th day there are only 6 Runs to accomplish the magic number of 90.

This is my idea on how to do this and I’m doing this manually and is a pain…

1. I have assigned the students numbers randomly 1-18 and separated them into three groups- Group 1 B3:B8 are numbers 15, 10, 2, 9, 1, and 8, Group 2 B9:B14 are numbers 12, 17, 16, 3, 18, and 5, Group 3 B15:B20 are numbers 11, 13, 4, 7, 6, and 14. I initially used the RAND and RANK to come up with the numbers for the 3 Groups.

2. On Sheet 1 I copy cells B9:B18 (these numbers are from Groups 2 and 3) and paste them on Sheet2 in cells A1 to A12. On Sheet2 A1:A12 are the paste cells from Sheet1 A9:A18. In cells B1:B12 is formula =RAND(),on Sheet2 in C1:C12 is formula =RANK(B1,$B$1:$B$12). I hit the F9 key to calculate the RAND formula and then sort Sheet2 range A1:C12 with the sort on column C smallest to largest.

3. I then copy from Sheet2 A1:A6 and paste it on Sheet3 A1:A6. On Sheet3 in cells A1:A6 are the numbers copied and pasted from Sheet2, on Sheet3 in cells B1:B6 is formula =RAND(),on Sheet3 in C1:C6 is formula =RANK(B1,$B$1:$B$6). I hit the F9 key to calculate the RAND formula and then sort Sheet3 range A1:C6 with the sort on column C smallest to largest.

4. From Sheet3 I copy cells A1:A6 and paste them on Sheet1 C3:C8.

5. The numbers that are not used from Sheet1 Group 2 are manually placed in Sheet1 C15:C20.

6. The numbers that are not used from Sheet1 Group 3 are manually placed in Sheet1 C9:C14.

7. I then copy Sheet1 B3:B8 and paste on Sheet3 A1:A6 and hit F9 to run the RAND and RANK formula. I then sort Sheet3 range A1:C6 with the sort on column C smallest to largest. I then manually place the numbers from Sheet3 cells A1:A6 on Sheet1 C9:C14 until all cells in range Sheet1 C9:C14 filled. The numbers remaining from Sheet3 cells A1:A6 are manually placed on Sheet1 cells C15:C20. All cells on Sheet1 cells C3:C20 should be filled.

8. I then copy and paste, 2 times, Sheet1 cells C9:C14 and cells C15:C20 to Sheet3 cells A1:A6 and hit the F9 key to run the RAND and RANK formulas. I then copy and paste Sheet3 cells A1:A6 back to Sheet1 cells C9:C14 and cells C15:C20.

9. In column D cells D38, D914 and D1520 I manually place the numbers that have not been used in the other cell ranges B3:B8, B9:B14, B15:B20, C3:C8, C9:C14, and C15:C20.

At this point all of the students have run each of the three positions and covers just 3 hours. As you can see this is very time consuming. I need to do this from column B to CM (90 times).

Is there an easier way to do this using a macro?

Best regards,
Charlie

				DAY 1
	Run 1	Run 2	Run 3	Run 4	Run 5	Run 6
LC	15	18	3	9	3	13
LC	10	6	4	10	4	14
LC	2	17	11	2	11	17
LC	9	7	5	15	12	7
LC	1	14	13	1	6	18
LC	8	16	12	8	5	16
GC	12	10	7	5	7	2
GC	17	4	14	12	14	11
GC	16	8	2	17	13	8
GC	3	15	9	18	10	6
GC	18	13	1	16	1	4
GC	5	11	6	3	9	15
LCM	11	12	16	7	18	12
LCM	13	9	17	14	2	3
LCM	4	2	18	11	16	10
LCM	7	3	8	4	8	9
LCM	6	5	15	13	17	1
LCM	14	1	10	6	15	5

[coliervile]

This might be easier to follow in color.

	Run 1	Run 2	Run 3	Run 4	Run 5	Run 6
LC	15	18	3	9	3	13
LC	10	6	4	10	4	14
LC	2	17	11	2	11	17
LC	9	7	5	15	12	7
LC	1	14	13	1	6	18
LC	8	16	12	8	5	16
GC	12	10	7	5	7	2
GC	17	4	14	12	14	11
GC	16	8	2	17	13	8
GC	3	15	9	18	10	6
GC	18	13	1	16	1	4
GC	5	11	6	3	9	15
LCM	11	12	16	7	18	12
LCM	13	9	17	14	2	3
LCM	4	2	18	11	16	10
LCM	7	3	8	4	8	9
LCM	6	5	15	13	17	1
LCM	14	1	10	6	15	5

				DAY 1
	Run 1	Run 2	Run 3	Run 4	Run 5	Run 6	Run 7
LC	15	18	3	9	3	13
LC	10	6	4	10	4	14
LC	2	17	11	2	11	17
LC	9	7	5	15	12	7
LC	1	14	13	1	6	18
LC	8	16	12	8	5	16
GC	12	10	7	5	7	2
GC	17	4	14	12	14	11
GC	16	8	2	17	13	8
GC	3	15	9	18	10	6
GC	18	13	1	16	1	4
GC	5	11	6	3	9	15
LCM	11	12	16	7	18	12
LCM	13	9	17	14	2	3
LCM	4	2	18	11	16	10
LCM	7	3	8	4	8	9
LCM	6	5	15	13	17	1
LCM	14	1	10	6	15	5

[coliervile]

Let's try this...

	Run 1	Run 2	Run 3	Run 4	Run 5	Run 6
LC	15	18	3	9	3	13
LC	10	6	4	10	4	14
LC	2	17	11	2	11	17
LC	9	7	5	15	12	7
LC	1	14	13	1	6	18
LC	8	16	12	8	5	16
GC	12	10	7	5	7	2
GC	17	4	14	12	14	11
GC	16	8	2	17	13	8
GC	3	15	9	18	10	6
GC	18	13	1	16	1	4
GC	5	11	6	3	9	15
LCM	11	12	16	7	18	12
LCM	13	9	17	14	2	3
LCM	4	2	18	11	16	10
LCM	7	3	8	4	8	9
LCM	6	5	15	13	17	1
LCM	14	1	10	6	15	5

[SamT]

As I see it, the hardest requirement to meet is the output table's layout. I can imagine one fairly short macro that would give a layout by hours, then a little bit more to reformat the sheet by Days.

Student / Hour	1	2	3	…	90
1	LC	GC	LCM		LCM
2	LCM	LC	GC		GC
3	GC	LCM	LC		LC
4	Etc
5
6
7
8
9
10
11
12
13
14
15
16
17
18

[coliervile]

Thank you SamT for your idea. Unfortunately I'm at home dealing with a water/flooding issue and when I get to work tomorrow I'll look at your idea.
Out of curiosity do you see any flaws in my idea with the exception that my way is manually done and harder to do?

[SamT]

I didn't analyze your manual method. Because it is a manual method

Since my last post, I have thought of a couple of fairly simple ways to generate a table sorted by workstation that use the Student By Hour table above as a source.

[coliervile]

SamT any idea that you have to offer I'm willing to look at.