Create meeting groups from list of attendee preferences

[Daveo]

Hi there,

I am trying to create a function that will automatically allocate
conference attendees to a meeting group, based on their preferences. I
have a spreadsheet with the following info:

Attendee Group1 Group2 Group3

Joe Bloggs 2 1 3
Jane Bloggs 1 3 2
Jimmy Bloggs 1 2 3

....and so on for several hundred attendees.

1 = First choice of meeting group to attend
2 = Second choice of meeting group to attend
3 = Third choice of meeting group to attend.

Let's say that each meeting group has a maximum occupancy of 50 people.
I want to automatically allocate people to Group 1 who have chosen it
as their first choice. When the group fills up, I would then like
anyone left over then to be allocated to their 2nd choice group and so
on.

I've no idea to get started with this sort of thing, but am fairly
comfortable with VBA and was wondering if anyone could give me any
pointers?

Many thanks - David

 

[Bernie Deitrick]

David,

I have developed a macro that does just that, though it does not optimize meeting attendance. By
that I mean, if a lot of people choose one specific meeting or class as their #3 choice, it will not
preferentially select those folks just to fill up the class - that is a judgement call on your part.
This is strictly based on attendee preferences, randomized to make it fair. To optimize class size
or to fill up other classes, you may need some manual intervention.

Anyway, let's say that you have three meetings.

In cells B11, enter the maximum allowed meeting size for the session.

In cells B22, enter the meeting names. In E2, enter the word "Randomize" and in F2, "Assigned to"

Then in B33, enter 0. In cell E3, enter -1.

Then starting in A4, enter the name, and in cells B44, the preference numbers. In cell E4, enter
the formula =RAND()

Continue down columns A through D with the names and preferences, and copy the formula in E4 down
column E to match your database.

Then save it, and run the macro below.

Note that this can be expanded for as many meetings as you have by inserting extra columns before
the column with the =RAND() formulas.

If the participants can have more than three choices, increase the 3 of the
For myChoice = 1 To 3
to match the maximum number of choices.

If you want to manually fill meetings, enter the values in the "Assigned to" column.

If you have a problem, email me privately and I will send you a working example.

HTH,
Bernie
MS Excel MVP

Sub AssignToSession()
Dim i As Integer
Dim j As Integer
Dim myChoice As Integer
Dim myC As Integer
Dim myR As Range
Dim myV As Range

Set myR = Intersect(Range("2:65536"), Range("A2").CurrentRegion)

On Error Resume Next
ActiveSheet.ShowAllData

myC = Range("IV1").End(xlToLeft).Column

For myChoice = 1 To 3
For i = 2 To myC
myR.Sort key1:=Cells(2, i), order1:=xlAscending, _
key2:=Cells(2, myC + 2), order2:=xlAscending, _
key3:=Cells(2, myC), order3:=xlAscending, header:=xlYes

myR.AutoFilter Field:=i, Criteria1:=myChoice
myR.AutoFilter Field:=myC + 2, Criteria1:="="

Set myV = myR.Columns(i).SpecialCells(xlCellTypeVisible)
If Cells(1, i).Value > 0 Then
If myV.Areas(2).Rows.Count < Cells(1, i).Value Then
myV.Areas(2).Offset(0, myC - i + 2).Value = myR(1, i).Value
Cells(1, i).Value = Cells(1, i).Value - myV.Areas(2).Rows.Count
Else
myV.Areas(2).Offset(0, myC - i + 2). _
Resize(Cells(1, i).Value).Value = myR(1, i).Value
Cells(1, i).Value = 0
End If
End If
myR.AutoFilter
Next i
Next myChoice

End Sub

 

[Daveo]

Hi Bernie,

Worked a treat!

Many thanks,

David

 

[Bernie Deitrick]

Thanks! Good catch!

You're right, and you get a gold star for paying better attention than me....

Bernie
MS Excel MVP