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

[@consumerdotorg 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 B1:D1, enter the maximum allowed meeting size for the session.

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

Then in B3:D3, enter 0. In cell E3, enter -1.

Then starting in A4, enter the name, and in cells B4:D4, 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" <writet...@gmail.com> wrote in message
news:1158677282.8...@e3g2000cwe.googlegroups.com...

[Daveo]

Hi Bernie,

Worked a treat!

Many thanks,

David

[skatonni]

Bernie

I think I followed the instructions but it appears there ia a typo in
this part of the code.

Code:
--------------------

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

--------------------

I cannot see a random factor unless I change key3.

Code:
--------------------
myR.Sort Key1:=Cells(2, i), Order1:=xlAscending, _

key2:=Cells(2, myC + 2), order2:=xlAscending, _

key3:=Cells(2, myC + 1), order3:=xlAscending, Header:=xlYes
--------------------

--
skatonni
------------------------------------------------------------------------
skatonni's Profile: http://www.officehelp.in/member.php?userid=4186
View this thread: http://www.officehelp.in/showthread.php?t=1203944

Posted from - http://www.officehelp.in

[@consumerdotorg Bernie Deitrick]

Thanks! Good catch!

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

Bernie
MS Excel MVP

"skatonni" <skatonn...@NoSpamPleaze.com> wrote in message
news:skatonn...@NoSpamPleaze.com...

[sch...@gmail.com]

I seem to be getting data that isn't correct. Say I have 5 participants and three meeting groups. If I assign a max size of zero for all meetings except for meeting group #1, which I assign a size of 5, I would expect all of them to be assigned to meeting group #1. Instead, they are assigned to all three groups in an order that does not make sense to me. For example:


Max Allowed Meeting size per session 5 0 0
Meeting Names Meeting 1 Meeting 2 Meeting 3 Randomize Assigned to
0 0 0 -1
Misty 2 3 1 0.54 Meeting 1
Sarah 3 1 2 0.00 Meeting 2
David 1 2 3 0.03 Meeting 1
John 1 2 3 0.65 Meeting 1
Sally 1 2 3 0.16 Meeting 3

[sch...@gmail.com]

Please disregard my previous post. I left the meeting names in my 'assigned to' column which kept them in those meetings even when I changed their preferences.

Complete user error.

Thank you so much for this resource!

[tripa...@gmail.com]

Hello

Could you please send me a working file? I have the iteration for the first session but how do I get the further iterations?

Thanks

[Sam Granger]

Wow this looks like exactly what I'm in need of but I've run the Macro typed above and it only assigns the first 5 into the first option and then stops. Can anyone help or send me a working version?

Thanks
Sam