Kieranjm

New Member
Joined
Sep 13, 2019
Messages
3
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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?
 
Upvote 0
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.
 
Upvote 0
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
ABCDEF
1Student NameChoice 1Choice 2Choice 3Choice 4Choice 5
2Name 1Act 1Act 2Act 3Act 4Act 5
3Name 2Act 2Act 3Act 4Act 5Act 1
4Name 3Act 3Act 4
5Name 4Act 1
6Name 5Act 2Act 3Act 4Act 5
7Name 6Act 3
8Name 7Act 4
9Name 8Act 5
10Name 9Act 3
11Name 10Act 2
12Name 11Act 2
13Name 12Act 3
14Name 13Act 1
15Name 14Act 3
16Name 15Act 1
17Name 16Act 2
18Name 17Act 3
19Name 18Act 2
20Name 19Act 2
21Name 20Act 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.
:eek: 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.



Rich (BB code):
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
ABCDEFGHIJKLMNO
1Student NameChoice 1Choice 2Choice 3Choice 4Choice 5Act 1Act 2Act 3Act 4Act 5
2Name 1Act 1Act 2Act 3Act 4Act 5Name 15*Name 20*Name 17*Name 7*Name 8*
3Name 2Act 2Act 3Act 4Act 5Act 1Name 13*Name 19*Name 14*Name 3**Name 11#
4Name 3Act 3Act 4Name 4*Name 18*Name 12*Name 5***
5Name 4Act 1Name 1*Name 16*Name 9*Name 2***
6Name 5Act 2Act 3Act 4Act 5Name 6*Name 10#
7Name 6Act 3
8Name 7Act 4
9Name 8Act 5
10Name 9Act 3
11Name 10Act 2
12Name 11Act 2
13Name 12Act 3
14Name 13Act 1
15Name 14Act 3
16Name 15Act 1
17Name 16Act 2
18Name 17Act 3
19Name 18Act 2
20Name 19Act 2
21Name 20Act 2
22
Allocate Choices
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top