Attached is a solution based on kvsrinivasamurthy's solution.
This version does split the class group in EQUAL groups (not 18 - 11 - rest as per your sample).
If the number of students is not divisible by 3, the remainer is added to group 1 and 2.
IE 39 students = 13 - 13 - 13
40 students = 14 - 13 - 13
41 students = 14 - 14 - 13
42 students = 14 - 14 - 14 ...
Option Explicit
Sub CreateGroups()
Dim Lr&, T&, Cnt&, cnt1&
Dim vMOD, vMOD1, vMOD2, c
Dim vGroup As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
For T = 2 To Lr
Cnt = WorksheetFunction.CountIf(Range("B" & T & ":B" & Lr), Range("B" & T))
vMOD = Cnt - (Cnt - Cnt Mod 3)
If vMOD > 0 Then vMOD1 = 1 Else vMOD1 = 0
If vMOD = 2 Then vMOD2 = 1 Else vMOD2 = 0
c = (Cnt - vMOD) / 3
Range("C" & T).Resize(c + vMOD1, 1) = 1
Range("C" & T + c + vMOD1).Resize(c + vMOD2, 1) = 2
Range("C" & T + c + vMOD1 + c + vMOD2).Resize(Cnt - c - vMOD1 - c - vMOD2, 1) = 3
T = T + Cnt - 1
Next T
End Sub
Bookmarks