HELP difficult sorting program HELP

Basically, the program needs to take a data set of X (i.e. 80) students and assign them them into Y (i.e. 10) groups. Then assign Y (i.e. 10) number of faculty members, one to each group. But, students can’t be in a group with anyone they have been in a group with before or have a faculty member they have had before.

Currently, there are three sets of groups that already exist. So, the program would need to be able to consult that data and use it to avoid repeats. Ideally, I want the program to be able to refer back to any number of past group lists, as the process will need to be repeated several times to generate several sets of groups. I'd like the past group lists to be in an excel spreadsheet format and the new lists that are outputted to also be into an excel spread sheet.

Also, I wanted to have the program be flexible so that the numbers of students, groups, and faculty members could change as well as the identities of each group (i.e. there may be times where some faculty are not involved and others are). Finally, I understand that there will be a point where the program cannot create a group without overlap. In this case I wanted to program to make the overlap from group sets more prior. For example, a student would be in the same group with another student they had been with in the first group set versus the third group set.

I had a few ideas as to how this could work, but I don’t know which idea to use and how to go about it.

1. I was thinking of inputting all the students into a pool and then assigning them one by one to a faculty member. Each time the program went to assign a student to a group/faculty member it would consult the past groups and make sure that the student had not been with that faculty member or any students that have already been assigned into this new group.

2. Another idea I had was to have the computer generate all the possible combinations of group sets (as in every possibility would be a list of Y groups using X students) , ignoring overlap (not sure how to do that mathematically). Then, it would go through each set checking for overlap of faculty member or student in comparison to previous lists. Each time it had overlap, it would go to the next group set, until it found a group without any overlap.

3. The last idea was similar to the second idea. The computer would generate all the possible combinations of groups (not sets), so if a group was 8 students and 1 faculty member, it would generate a list of every possible group. Then, it would go through each set checking for overlap from group to group within those sets. It would also have to check against the current groups it sets aside for the group set being generated.

I hope someone can find a solution as I’ve been doing this process by hand on paper and its very tedious and time consuming. Please keep in mind that my programming knowledge is limited (I have only done a little Visual Basic programming, and that was several years ago) and I was thinking of using Visual Basic or an Excel macro. Thanks in advance for any help you can provide!


Ryan