Mentoring Schedule

[wilson1313]

I run a small mentoring program with 16 students.

We have them for a period of 13 weeks, 6 of which are in different departments within a corporation.

I would like to randomize the students in departments to force them to interact with other students. So that student number 1 does not always go to the same department with student 7 etc.

I attached a started excel but have no clue. I looked at the random sports schedule generator but got lost.

Thanks,

[natefarm]

With credit to ideas I got from here http://www.excelforum.com/excel-gene...uplicates.html

Do the following:
1. Enter your student names in cells C2:R2.
2. Enter your dates in A3:A8
3. Enter your department names in C3:C8 (under the 1st student).
4. In B3, enter the formula =RAND(), and drag it down to B8.
5. Plug the following into a code module and run it.

Please Login or Register  to view this content.

If you don't like the results, rerun as often as you like.

[wilson1313]

Thanks for the reply. It works as you suggested.

Can I add one more wrinkle? I would like to keep the number of students in each department maximum of 3. The randomizer has 4 sometimes and none at times.

[natefarm]

Well, that made it a little more tricky, but I think I got it. Let's start from scratch, just to make sure it's all set up correctly.

1. Enter your student names in cells D2:S2 (Shifted over from before)
2. Enter your dates in A3:A8
3. Enter your department names in C3:C8 (no longer under the 1st student).
4. In B3, enter the formula =RAND(), and drag it down to B8.
5. In T3, enter =COUNTIFS(D3:S3,C3), and drag it down to T8.
6. In T9, enter =MAX(T3:T8).
7. Plug the following into a code module and run it.

Please Login or Register  to view this content.

I ran it a number of times, and it never hit the Fail line.

[wilson1313]

Nate,

Thanks a million. It works like a charm. The students in the mentoring program owe you a big thanks.