Student Choice

[Kieranjm]

Hi,

I am trying to make a process a little easier at the school I work in. 1 hour a week we have an enrichment time where different activities are offered to the children on a 6 week basis, they pick their preferences on a slip, then 2 staff members sift through all the slips and allocate them. Is there a way to enable each class to input the choices, then to automatically populate the 14 or so activities with the children taking into account their preference and the maximum group size? The order in which children are allocated could either be defined by class or random across all classes. There are 6 classes, so about 180 students to be allocated to 12-15 activities, most activities have a maximum of 12, some have more.

I have seen some other solutions similar, but with smaller groups to allocate to and larger maximums, so I was unsure how to adapt some of the code.

Any help would be greatly appreciated.

 

[Peter_SSs]

Welcome to the MrExcel board!

1. Do the students get to provide more than one preference?
- If so, how many preferences are they allowed to give?
- If not, and they miss out on their preference, do they just get allocated to any activity that has space available?

2. For example, if Activity A only has one student choosing it and Activity B only has 3 students choosing it and there are 4 other students who missed their choice altogether, must those 4 students get allocated 3 to Activity A and 1 to Activity B so there are 4 in each or can they get allocated any way we like (for example all 4 to Activity A)?

3. Would your answer to question 2 be different if Activity A had a limit of 5 and Activity B had a limit of 15?

I'm not sure I will have an answer for you anyway, but the above will help clarify the requirement. Would a macro solution be acceptable if one can be found?

 

[Kieranjm]

Hi,

1. Students can choose a preference for all of them but usually just do the top 5 choices.

2/3. It would be better for them to be allocated evenly so that all activities can run. Human intervention can be used to decide if its too few, for example if only 4 students choose football, it wouldn't work, but if 4 students end up in fencing it would be fine.

I think a macro would be acceptable.

 

[Peter_SSs]

OK, let's see if this is any use.

In my sample, I have 20 students and 5 possible Activities and each student can have a maximum of 5 choices. Those choices are entered in A1:F21 as shown below.

Excel Workbook
	A	B	C	D	E	F
1	Student Name	Choice 1	Choice 2	Choice 3	Choice 4	Choice 5
2	Name 1	Act 1	Act 2	Act 3	Act 4	Act 5
3	Name 2	Act 2	Act 3	Act 4	Act 5	Act 1
4	Name 3	Act 3	Act 4
5	Name 4	Act 1
6	Name 5	Act 2	Act 3	Act 4	Act 5
7	Name 6	Act 3
8	Name 7	Act 4
9	Name 8	Act 5
10	Name 9	Act 3
11	Name 10	Act 2
12	Name 11	Act 2
13	Name 12	Act 3
14	Name 13	Act 1
15	Name 14	Act 3
16	Name 15	Act 1
17	Name 16	Act 2
18	Name 17	Act 3
19	Name 18	Act 2
20	Name 19	Act 2
21	Name 20	Act 2
22
Allocate Choices

In the code below the 'Const' line towards the top lists the available activities and the maximums allowed in each activity.
I have assumed that the total of all the maximums allowed for the activities will be greater than or equal to the number of students, If not this code will 'hang' in an infinite loop so be careful with that.
A safety net would be to add an 'Overflow' activity at the end of that Const line and give it a maximum value larger than your total students, say 500 since you say only about 180 students.

Sub Allocate_To_Activities()
  Dim dChoices As Object, dActivities As Object
  Dim a As Variant, itm As Variant, dKey As Variant
  Dim i As Long, j As Long, uba2 As Long
  Dim sChoice As String, sName As String
  
  Const ActLimits As String = "Act 1,4|Act 2,4|Act 3,5|Act 4,6|Act 5,3" '<- Edit as needed
  
  Set dChoices = CreateObject("Scripting.Dictionary")
  Set dActivities = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 6).Value
  uba2 = UBound(a, 2)
  For i = 1 To UBound(a)
    For j = 2 To uba2
      If Len(a(i, j)) > 0 Then dChoices(a(i, 1)) = dChoices(a(i, 1)) & "|" & a(i, j)
    Next j
  Next i
  For Each itm In Split(ActLimits, "|")
    dActivities(Split(itm, ",")(0)) = Split(itm, ",")(1) & "|"
  Next itm
  For j = 2 To uba2
    For i = dChoices.Count To 1 Step -1
      sName = dChoices.Keys()(i - 1)
      sChoice = Split(dChoices(sName), "|")(1)
      If Len(sChoice) > 0 Then
        If Val(dActivities(sChoice)) > 0 Then
          dActivities(sChoice) = (Val(dActivities(sChoice)) - 1) & Mid(dActivities(sChoice), InStr(1, dActivities(sChoice), "|")) & sName & String(j - 1, "*") & "|"
          dChoices.Remove sName
        Else
          dChoices(sName) = "|" & Split(dChoices(sName) & "|", "|", 3)(2)
        End If
      End If
    Next i
  Next j
  Do Until dChoices.Count = 0
    For Each dKey In dActivities.Keys
      If Val(dActivities(dKey)) > 0 Then
        dActivities(dKey) = (Val(dActivities(dKey)) - 1) & Mid(dActivities(dKey), InStr(1, dActivities(dKey), "|")) & dChoices.Keys()(0) & "#|"
        dChoices.Remove dChoices.Keys()(0)
        If dChoices.Count = 0 Then Exit For
      End If
    Next dKey
  Loop
  With Range("K1").Resize(, dActivities.Count)
    .Value = dActivities.Keys()
    For i = 1 To .Columns.Count
      sName = Split(dActivities.Items()(i - 1), "|", 2)(1)
      If Len(sName) > 0 Then
        a = Split(Split(dActivities.Items()(i - 1), "|", 2)(1), "|")
        .Cells(2, i).Resize(UBound(a)).Value = Application.Transpose(a)
      End If
    Next i
  End With
End Sub

The above code has produced the results you see below in columns K:O.
The asterisks after each name indicate which choice that student got
* = 1st choice
** = 2nd choice etc
# = Student was unable to be given any of their choices so were allocated to a group with space. As you can see, I have not been able to 'even' out the groups when allocating those '#' students but at least they are identified for you to manually adjust if you want. Similarly, the number of asterisks may also help if you want to shuffle students about a bit.
Of course if the # and * are a problem for you they can be removed from the code.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Student Name	Choice 1	Choice 2	Choice 3	Choice 4	Choice 5					Act 1	Act 2	Act 3	Act 4	Act 5
2	Name 1	Act 1	Act 2	Act 3	Act 4	Act 5					Name 15*	Name 20*	Name 17*	Name 7*	Name 8*
3	Name 2	Act 2	Act 3	Act 4	Act 5	Act 1					Name 13*	Name 19*	Name 14*	Name 3**	Name 11#
4	Name 3	Act 3	Act 4								Name 4*	Name 18*	Name 12*	Name 5***
5	Name 4	Act 1									Name 1*	Name 16*	Name 9*	Name 2***
6	Name 5	Act 2	Act 3	Act 4	Act 5								Name 6*	Name 10#
7	Name 6	Act 3
8	Name 7	Act 4
9	Name 8	Act 5
10	Name 9	Act 3
11	Name 10	Act 2
12	Name 11	Act 2
13	Name 12	Act 3
14	Name 13	Act 1
15	Name 14	Act 3
16	Name 15	Act 1
17	Name 16	Act 2
18	Name 17	Act 3
19	Name 18	Act 2
20	Name 19	Act 2
21	Name 20	Act 2
22
Allocate Choices

 

[Kieranjm]

This is amazing, thank you so much!!

I've just adapted it and worked out the possible roll out and it works brilliantly. The best part is it allows the order to be adjusted for different classes or groups to get priority at different times of the year.

You have saved us a lot of time!

 

[Peter_SSs]

You're welcome. Glad it met your needs.

Yes, as written, people at the bottom of the list are getting higher priority than those at the top. And of course Choice 1 gets a higher priority than Choice 2 etc.