Assigning students to lessons

[Stainless02]

Good morning all, please stay with me on this. I am after formulas or macros that have some flexibility for timetabling and lesson allocation...

The situation is I have up to 32 students (learning to be instructors) and have a bank of lessons. The first step I would like to achieve is automatically (randomly) pairing students and then automatically allocating a lesson to that pair. I would like it to keep cycling through the lesson list until all pairs are allocated.

I would like the lesson and student list to be dynamic so that it future proofs the workbook as lessons and student numbers change and an awesome addition would be, to have a couple of lesson banks so that we can progressively allocate harder lessons as they progress through course.

Not sure if this is a fantasy or not but thought I would ask some experts.

Cheers

 

[frabulator]

I do something similar for my students with assigned seats.

What I have done was to create a random number generator that does not allow duplicates. This can be done by using arrays or collections. This will be a selection of students as each student will be assigned a number (could be alphabetically to make it easier).

Decided how many per group, or take your number of projects and divide it by your number of students so you know how many per each group.

Next create a second list (either in an array or collection) and do the same thing, randomly assigning the project for the students in a random order.

Here is an example of how to create a random list from 1 to 32 and put the list into a collection without having duplicates.

You should be able to modify this to get you to where you need to go.

Public Sub GenerateRandomNumNoDuplicates()
    lowerbound = 1
    upperbound = 32
    Set StudentsCol = New Collection
  
    For i = 1 To 5
        i = 1
        If StudentsCol.Count >= upperbound Then Exit For
      
        Randomnumber = Math.Round((upperbound - lowerbound + 1) * Rnd + lowerbound)
        Dim Found As Boolean
        Found = False
      

        Dim vItem As Variant
        For Each vItem In StudentsCol
            If vItem = Randomnumber Then
                Found = True
                Exit For
              
            End If
          
        Next

      
        If Found = False Then StudentsCol.Add Randomnumber
    Next


   
    Dim Str As String
    Dim pItem As Variant
    For Each pItem In StudentsCol
        Str = Str & pItem & vbNewLine
    Next
   
    MsgBox ("Here is your list:" & vbNewLine & Str)
   
  
End Sub