VBA Function to Randomly Assign Customers to Staff

This guide provides a complete solution to assign 4,655 customers from Sheet1 to 10 staff members in Sheet2 based on their availability (shortages).

Problem Overview

• Input:
  • Sheet1 contains 4,655 customers.
  • Sheet2 contains 10 staff members in Column A and their respective shortages in Column B.
• Output:
  • Assign each customer in Sheet1 a staff number in Column J based on the shortages.

VBA Code

Sub AssignCustomersToStaff()
    ' This subroutine randomly assigns customers to staff members based on their shortage.

    Dim wsCustomers As Worksheet
    Dim wsStaff As Worksheet
    Dim totalCustomers As Long
    Dim totalStaff As Long
    Dim staffShortages() As Variant
    Dim i As Long, j As Long
    Dim assignCount As Long
    
    ' Set worksheets
    Set wsCustomers = ThisWorkbook.Sheets("Sheet1")
    Set wsStaff = ThisWorkbook.Sheets("Sheet2")

    ' Get the total number of customers
    totalCustomers = wsCustomers.Cells(wsCustomers.Rows.Count, "A").End(xlUp).Row
    
    ' Get staff member count
    totalStaff = wsStaff.Cells(wsStaff.Rows.Count, 1).End(xlUp).Row
    
    ' Read shortages into an array for processing
    ReDim staffShortages(1 To totalStaff)
    For i = 1 To totalStaff
        staffShortages(i) = wsStaff.Cells(i, 2).Value ' Shortages in column B
    Next i
    
    ' Loop through each customer for assignment
    For i = 1 To totalCustomers
        Dim assignedStaff As Integer
        Dim staffAvailable As Collection
        Set staffAvailable = New Collection
        
        ' Populate available staff based on shortages
        For j = 1 To totalStaff
            If staffShortages(j) > 0 Then
                staffAvailable.Add j ' Add staff index to available staff
            End If
        Next j

        ' Randomly assign a staff member if available
        If staffAvailable.Count > 0 Then
            assignedStaff = staffAvailable(Int((staffAvailable.Count) * Rnd) + 1)
            wsCustomers.Cells(i, 10).Value = wsStaff.Cells(assignedStaff, 1).Value ' Output staff number in column J
            staffShortages(assignedStaff) = staffShortages(assignedStaff) - 1 ' Decrement the shortage
        Else
            wsCustomers.Cells(i, 10).Value = "No Staff Available"
        End If
    Next i
    
    ' Optional: Inform the user upon completion
    MsgBox "Customer assignments complete!", vbInformation

End Sub

Code Explanation

1. Worksheet Setup:

   • Set references to the customer and staff worksheets.

2. Read Input Data:

   • Calculate the total number of customers and staff members.
   • Store the staff shortages in an array for easy access.

3. Customer Assignment Logic:

   • Loop through each customer.
   • Create a collection of available staff indices based on their remaining shortage.
   • Randomly select a staff index from the available staff and assign it to the customer.
   • Update the shortage for the assigned staff.

4. Output:

   • Assign the staff number to the customer's appropriate column (Column J) based on the staff member chosen.

5. Completion Message:

   • A message box to inform users when the assignment is complete.

Usage Example

1. Open Excel and create a new workbook.
2. In Sheet1, populate Column A with customer identifiers (A1:A4655).
3. In Sheet2, list staff identifiers in Column A (e.g., A1:A10) and their respective shortages in Column B (B1:B10).
4. Press ALT + F11 to open the VBA editor, insert a module, and paste the code above.
5. Close the VBA editor and run the macro AssignCustomersToStaff.

Conclusion

This code effectively assigns 4,655 customers to staff members based on their availability using a fair randomization method. You can customize the staff names and shortage values as needed. For further learning in VBA and data analysis, consider exploring courses from the Enterprise DNA Platform to enhance your skills.