|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
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" <writetodaveo[ at ]gmail.com> wrote in message
news:1158677282.891764.121330[ at ]e3g2000cwe.googlegroups.com...
[Quoted Text] > 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
>
|
|
Hi Bernie,
Worked a treat!
Many thanks,
David
|
|
|