Allocation of accounts to users equally

Hi,
I have excel inventory file with many number of accounts as input & I have 20 users in mapping sheet.
Task is to allocate accounts to each user equally & for each user the maximum target of allocation is 50 so if one user is allocated 50, again we should not allocate accounts to that user.
Kindly suggest an solution for the above scenario, if possible kindly create a code using UiPath

To solve this problem using UiPath, you can create a workflow that reads the accounts from the inventory file, allocates them equally among the 20 users, and ensures that no user is allocated more than 50 accounts. Below is a step-by-step guide and a sample UiPath workflow to achieve this:


Steps to Implement in UiPath:

  1. Read the Inventory File:
  • Use the “Read Range” activity to read the accounts from the Excel file into a DataTable.
  1. Read the Mapping Sheet:
  • Use another “Read Range” activity to read the list of users from the mapping sheet into another DataTable.
  1. Initialize Variables:
  • Create a variable UserIndex (type Int32) to keep track of the current user being allocated accounts.
  • Create a variable AllocationCount (type Dictionary<String, Int32>) to store the number of accounts allocated to each user.
  1. Allocate Accounts:
  • Use a “For Each Row” activity to loop through the accounts in the inventory DataTable.
  • For each account, assign it to the current user (using UserIndex) and increment the allocation count for that user in the AllocationCount dictionary.
  • If a user reaches 50 allocations, move to the next user by incrementing UserIndex.
  1. Write the Output:
  • Use a “Write Range” activity to write the allocated accounts (with the assigned user) to a new Excel file or sheet.

Sample UiPath Workflow Code:

Here’s a high-level implementation of the workflow:

  1. Read Inventory and Mapping Sheets:
  • Use two “Read Range” activities to read the inventory and mapping sheets into DataTables (dtInventory and dtUsers).
  1. Initialize Variables:
  • UserIndex = 0
  • AllocationCount = New Dictionary(Of String, Int32)
  • Loop through dtUsers and initialize the dictionary with each user’s name and a count of 0.
  1. Allocate Accounts:
  • Use a “For Each Row” activity to loop through dtInventory.
  • Inside the loop:
    • Check if the current user (dtUsers.Rows(UserIndex)("UserName")) has reached 50 allocations using the AllocationCount dictionary.
    • If yes, increment UserIndex and reset if it exceeds the number of users.
    • Assign the account to the current user and update the AllocationCount dictionary.
    • Add a new row to an output DataTable (dtOutput) with the account and assigned user.
  1. Write Output:
  • Use a “Write Range” activity to write dtOutput to a new Excel file or sheet.

Example Code in UiPath:

Here’s a more detailed breakdown of the workflow:

Initialize Variables:

  • Use an “Assign” activity to initialize UserIndex and AllocationCount.

UserIndex = 0 AllocationCount = New Dictionary(Of String, Int32) For Each row In dtUsers.Rows AllocationCount.Add(row(“UserName”).ToString(), 0) Next

Allocate Accounts:

  • Use a “For Each Row” activity to loop through dtInventory.

For Each row In dtInventory.Rows ’ Check if the current user has reached 50 allocations If AllocationCount(dtUsers.Rows(UserIndex)(“UserName”).ToString()) >= 50 Then UserIndex = (UserIndex + 1) Mod dtUsers.Rows.Count End If ’ Assign the account to the current user Dim newRow As DataRow = dtOutput.NewRow() newRow(“Account”) = row(“Account”).ToString() newRow(“AssignedUser”) = dtUsers.Rows(UserIndex)(“UserName”).ToString() dtOutput.Rows.Add(newRow) ’ Update the allocation count for the current user AllocationCount(dtUsers.Rows(UserIndex)(“UserName”).ToString()) += 1 Next

Write Output:

  • Use a “Write Range” activity to write dtOutput to a new Excel file or sheet.

Output:

The output will be a new Excel file or sheet with each account assigned to a user, ensuring no user gets more than 50 accounts.


Notes:

  • Ensure the dtOutput DataTable has columns for “Account” and “AssignedUser”.
  • Handle edge cases, such as when the number of accounts is not perfectly divisible by the number of users.

This workflow should solve your problem efficiently. Let me know if you need further clarification!