Excel/Work Allocation macro
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.
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