How to split a list of random numbers equally

[Peter Arre]

I have 250 students in my boarding school to be assigned randomly to 36 dining tables. I have written a Rnd function that does that but, for some tables, it assigns more students than others. How do I rewrite my function to atleast assign equal number of students to each table? Peter Arre, Papua New Guinea

[Rabbit]

Basically, the steps would be:

1) Assign each person a random number
2) Sort by the random number
3) Sequentially number the sorted list
4) Then you can mod the sequential number to assign them to a table or you can just assign them in ranges

[NeoPa]

1. Assign all your students, in any order, to an array of n elements where n is the number of students you have (250 in this case).
2. Cycle through your tables one at a time.
3. For each table take a new random number and multiply it by the number of students left to assign (Starts at 250 but reduces for each student assigned).
4. Using this value as an index into the student array take the indicated student and add it to the table.
5. Replace the recently selected student in the array with the last student in the array.
6. Reduce the array elements by one (or simply use only the array elements covered by the new value of unassigned students).
7. Continue until all students have been assigned.

[Peter Arre]

Thanks

Basically, the steps would be:

1) Assign each person a random number
2) Sort by the random number
3) Sequentially number the sorted list
4) Then you can mod the sequential number to assign them to a table or you can just assign them in ranges

Thank you VM your tip is pointing me in the right direction P. Arre

[ADezii]

I created some Quick Code that Randomizes Student IDs (between 0 and 249), places them into an Array, checks for Duplicates, then sequentially assigns these Student IDs to each of the 36 Tables. The Code can definitely be improved upon, especially since it assigns 7 Random Students to each of the first 35 Tables (245 Students), then leaves the remaining 5 for the last Table (total of 250). I'll post the Code below, along with the generated Output. This should, at least, point you in the right direction.

'Assign 250 Students Randomly and relatively evenly to 36 Tables
'************************* User Defined Section *************************
Const conNUMBER_OF_STUDENTS As Byte = 250
Const conNUMBER_OF_TABLES As Byte = 36
Const conSTUDENTS_PER_TABLE As Byte = 7
'************************************************************************

Dim intCtr As Integer
Dim intCtr_2 As Integer
Dim aintStudents(0 To conNUMBER_OF_STUDENTS - 1) As Integer

'The 1st 35 Tables will have 7 Students per Table, Tables 36 will have 5
'Randomize the 250 Students, populate Array wiith the Results
For intCtr = 0 To conNUMBER_OF_STUDENTS - 1
  aintStudents(intCtr) = Int(Rnd * conNUMBER_OF_STUDENTS)
Next

'Lets eliminate the Duplicates
DoItAllOverAgain:
For intCtr = 0 To UBound(aintStudents)
  For intCtr_2 = 0 To UBound(aintStudents)
    If intCtr <> intCtr_2 Then
      If aintStudents(intCtr) = aintStudents(intCtr_2) Then
        aintStudents(intCtr) = Int(Rnd * conNUMBER_OF_STUDENTS)
          GoTo DoItAllOverAgain
      Else
      End If
    End If
  Next intCtr_2
Next intCtr

Debug.Print "Number of Students: " & conNUMBER_OF_STUDENTS
Debug.Print "Number of Tables: " & conNUMBER_OF_TABLES
Debug.Print "Students per Table: " & conSTUDENTS_PER_TABLE

'aintStudents() now contains 250 Random and Unique Values between 0 and 249
'Let's now assign to the 36 Tables
  For intCtr_2 = 0 To conNUMBER_OF_STUDENTS - 1
   If intCtr_2 Mod conSTUDENTS_PER_TABLE = 0 Then Debug.Print "*********************************"
     Debug.Print Format(intCtr_2 + 1, "000") & " - Student #" & Format$(aintStudents(intCtr_2) + 1, "000") & _
                 " ==> " & "Table #" & Format$(intCtr_2 \ conSTUDENTS_PER_TABLE + 1, "00")
  Next
  Debug.Print "*********************************"

OUTPUT:

Number of Students: 250
Number of Tables: 36
Students per Table: 7
*********************************
001 - Student #007 ==> Table #01
002 - Student #036 ==> Table #01
003 - Student #221 ==> Table #01
004 - Student #160 ==> Table #01
005 - Student #032 ==> Table #01
006 - Student #122 ==> Table #01
007 - Student #097 ==> Table #01
*********************************
008 - Student #142 ==> Table #02
009 - Student #050 ==> Table #02
010 - Student #168 ==> Table #02
011 - Student #244 ==> Table #02
012 - Student #145 ==> Table #02
013 - Student #062 ==> Table #02
014 - Student #031 ==> Table #02
*********************************
015 - Student #049 ==> Table #03
016 - Student #178 ==> Table #03
017 - Student #088 ==> Table #03
018 - Student #166 ==> Table #03
019 - Student #131 ==> Table #03
020 - Student #109 ==> Table #03
021 - Student #018 ==> Table #03
*********************************
022 - Student #192 ==> Table #04
023 - Student #161 ==> Table #04
024 - Student #058 ==> Table #04
025 - Student #132 ==> Table #04
026 - Student #194 ==> Table #04
027 - Student #234 ==> Table #04
028 - Student #185 ==> Table #04
*********************************
029 - Student #203 ==> Table #05
030 - Student #180 ==> Table #05
031 - Student #138 ==> Table #05
032 - Student #022 ==> Table #05
033 - Student #237 ==> Table #05
034 - Student #247 ==> Table #05
035 - Student #099 ==> Table #05
*********************************
036 - Student #239 ==> Table #06
037 - Student #213 ==> Table #06
038 - Student #143 ==> Table #06
039 - Student #125 ==> Table #06
040 - Student #054 ==> Table #06

'Output initentially omitted

*********************************
246 - Student #041 ==> Table #36
247 - Student #177 ==> Table #36
248 - Student #038 ==> Table #36
249 - Student #068 ==> Table #36
250 - Student #121 ==> Table #36
*********************************

[Peter Arre]

I can but simply say THANK YOU. This is exactly what I needed. I am indeed greatly humbled by the fact that you have provided the code and its output. P. Arre

[ADezii]

You are quite welcome. You already got the ball rolling, I just gave it a little push.