Workshop Asssignment

[Peterfinnerty]

Hi all,

I'm trying to assign a list of people to workshops. I'm running an event with two workshop times. Since the workshops duplicate, there are two opportunities for people to get their first, second, or third choice. For example, if John Smith's first choice preference was full for the first time slot and open in the second, he would be assigned to the second. In that instance if both slots were full, he would be assigned to his second choice (if it was open). Every person gets three choices, so this should (ideally) assign them to two of their choices. When there is no availability, they will be listed on a separate worksheet.

There are a few complicated issues that I'm struggling with, so I'll list the features I need:

1. Preference is given in order of the list
2. People have to be placed into two workshops
3. The code should assign someone to one workshop and then assign the next person. This could mean that they are assigned to something in the first session or second session.
4. I need a separate list of people who do not get their choices (ideally for one or two of their sessions)
5. Workshops are limited to 25 people

Here's an example of the sheet with people:

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding:0px; mso-ignoreadding; color:black; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style>[TABLE="width: 260"]
<tbody>[TR]
[TD="width: 65"]A[/TD]
[TD="width: 65"]B[/TD]
[TD="width: 65"]C[/TD]
[TD="width: 65"]D[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]First Code[/TD]
[TD]Second Code[/TD]
[TD]Third Code[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Brad Ryan[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Jessica Grey[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Here's the stuff I have worked through so far. I'm not so sure how helpful it will be. I based it on some things I found on here.

Sub Macro1()
‘Rules: Look at first choice, if that is full, put them in next choice, and if that’s full put in next for each workshop
'Selection limits
Limit = 25
'Last row
lr = Cells(Rows.Count, B).End(xlUp).Row 'edit A and change it to the last column
For r = 2 To lr  
    For col = 1 To 3
        Select Case Cells(r, col) 'deal with choice
            Case “Firstone”
                If AA < Limit Then  ‘AA= First choice, first session
                CAA = AA + 1  'increment count for A
                Cx = CAA  'set count to use
                Else
                GoTo Nxt  'otherwise no availability try next choice
                End If
            Case “Firsttwo”
                If AB < Limit Then
                CAB = AB + 1      ‘AB= First choice, second session. The variables continue in this pattern
                Cx = CAB
                Else
                GoTo Nxt
                End If
            Case “Secondone”
                If BA < Limit Then
                CBA = BA + 1
                Cx = CBA
                Else
                GoTo Nxt
                End If
              Case “Secondtwo”
                If BB < Limit Then
                CBB = BB + 1
                Cx = CBB
                Else
                GoTo Nxt
                End If
              Case “Thirdone”
                If CA < Limit Then
                CCA = CA + 1
                Cx = CCA
                Else
                GoTo Nxt
                End If
              Case “Thirdtwo”
                If CB < Limit Then
                CCB = CB + 1
                Cx = CCB
                Else
                GoTo Nxt
                End If
            Case Else
            End Select
        
            If Cx > Limit Then  '? no vacancy for choice
                Exit For  'if no vacancy quit loop
                Else  'pass detail to sheet of choice
                Sheets("Choice " & Cells(r, col)).Cells(Cx + 1, 2) = Cells(r, 2)
                Sheets("Choice " & Cells(r, col)).Cells(Cx + 1, 3) = Cells(r, 3)
                Exit For
            End If

 

[Peterfinnerty]

For clarification, "First code" = First choice, "Second Code"= Second Choice, etc.
The choices were text values so I reassigned them to these codes.

 

[Peterfinnerty]

I have sorted out a system for a list of people not assigned to workshops. It uses if statements in a separate sheet.

What remains is to assign people to workshops based on their preference. Any help on the VBA with this would be appreciated. Here is the worksheet as it stands:

[TABLE="width: 416"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]First Choice[/TD]
[TD]Second Choice[/TD]
[TD]Third Choice[/TD]
[TD]First Session[/TD]
[TD]Second Session[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brad Ryan[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jessica Grey[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


When the VBA outputs something, it can put the number from column B, C, or D (depending on what choice is open) into E or F.
I have two worksheets called "Session One Count" and "Session Two Count" where each workshop has a sum of columns E and F is the code matches the workshop number. This will supply the limit (25).

The VBA can skip assignment for people whose choices are filled up. The if statements are set up to gather a list of people with blanks in their choices.

Again, any help would be appreciated.