You are here:

Excel/Work Allocation macro


Dear Expert,

greetings of the day!!

I have written a macro that select samples out of complete master file. Next step is to allocate complexity wise work uniformly between workers who are present.

I have a worksheet named ‘work allocation’ that contains rows of data items divided into four categories say High, Medium, Low, Very Low(or default if value is not matching with other categories).

There is a worksheet Manager UI, in which one can select number of workers who are present in that shift.

What I am trying to do is, to write a macro, that write names of workers available in a column say “A” against all the rows in worksheet work allocation so that all workers get nearly equal complexity work. Number of rows are in the range of 1 to 5 mn.

e.g :

let There are 3 workers selected in cross box tick ( A B C )
and there are 100 rows for High : 33 will get A , 33 will get B , 34  will get C

and so on for all complexity workers

i can share my original excel, if it can be of any help in saving time for you.

Please guide with similar code that I can modify. Not very good in Vb but can make minor corrections.



Seems like you could just do this by sorting your data into complexity, then using a formula to break you data into 3rds or 4ths or 5th or however many shift workers you have.  So I have provided pseudocode that assumes the data is sorted by complexity and starts with high in row 1.    

You can get the number of say "High" values with


then divide that by 3 (or 4 or 5)

countif(B:B,"High")/3   as an example.

so here is some pseudocode that represents the type of algorithm I would pursue to do this.  
You don't give me any specifics about your data so that is about the best I can do and is consistent with the intent of this forum to share technical knowledge.  

Dim shiftcnt as long, highcnt as long, highshiftcnt as long
Dim I as long, j as long, k as long

shiftcnt = 3 ' replace with code to get your cross box tick
highcnt = application.countif(Range("B:B"),"High")
highshiftCnt = Application.Rounddown(highcnt/ shiftcnt,0)

dim v(1 to shiftcnt) ' Replace with code that gets your shift names
v(1) = "Navin"
v(2) = "Rajeev"
v(3) = "Sasoon"

j = 1
k = 1
for I = 1 to highcnt
 cells(j, "A").Value = v(k)
 j = j + 1
 ' so we incremented j but if it is higher than the number of items
 ' for an individual worker, we need to set j back to 1 and
 ' increment the index into the individual worker array so we assign
 ' the next highshiftcnt items to the next worker.  However, since we
 ' rounded down, for the last worker they may exceed the highshiftcnt
 ' pick up all the remaining high complexity work rows
 if j > highshiftcnt and k <> shiftcnt then
   j = 1
   k = k + 1
 end if

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.