Random Selection in Column w/ No Repetition in Row

P

peerogel

Board Regular
Joined
Jan 25, 2011
Messages
108
I need help creating a schedule that generates random selections with limited repetitions. The selections will go down the column with limited repetitions down the row. I thought I was on the right track but I'm stuck. https://www.mrexcel.com/forum/excel-questions/1064540-random-selection-repetition-rows.html

I was trying to figure the random selection thing and add from there, but cant figure out how to get the pre-assigned days off. I hoping I could skip the selection if the cell was not empty but that only ends the random selection for the row.

I hope I can get some guidance, im trying to make something like the table below. The x's represent the days off. Times would be how many times that item on the list has to be used on the column for the day. Thanks in advance.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD][/TD]
[TD][/TD]
[TD]List[/TD]
[TD]Times[/TD]
[/TR]
[TR]
[TD]Employee1[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]training[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]
Employee2​
<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L11[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]
Employee3​
<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L12[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]
Employee4​
<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L13[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]
Employee5​
<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]vacation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L14[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]
Employee6​
<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]
Employee7​
<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]leave[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L16[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]
Employee8​
<strike></strike>[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L17[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]
Employee9​
<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L18[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]
Employee10​
<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L19[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]
Employee11​
<strike></strike>[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L20[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]
Employee12​
<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]testing[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TRA[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]
Employee13​
<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WK[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]
Employee14​
<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SP[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Your thread has had about 70 'views'. That fact that none of those viewers have replied suggests that, like me, none of the viewers can work out what you actually want. :eek:

Does what you have posted include your expected results?
a) If so, which are those results?
b) If not, what are the actual expected results and where would they be located? (Perhaps another table showing this?)
For either a) or b) explain how the expected results are decided manually.
I know you are looking for something random so the expected results may not be fixed but hopefully you could provide & explain one possible set of results.
 
Last edited:
Upvote 0
Thanks for taking the time to help. No, the table I posted before does not have the results I'm looking for. The employee list is actually longer but the list below is what im trying to get. I only did one week. But to give you an example: Everyday should have at least 1:L11, 2:L12, 1:L13, 1:L14, 2:L15, 1:L16, 1:L20, 1:TRA, 1:WK, 1:SP. It should take the list its on file and ignore any manual entry (like the "X", "training", or "vacations"). I did one week and I was already getting repetitions.
I was trying to make it to where i can change the values every two weeks. I hope this makes more sense . I know it will be impossible to have no repetitions, but im trying to get minimum repetitions.

[TABLE="width: 743"]
<tbody>[TR]
[TD="width: 103, bgcolor: transparent"]Employee
[/TD]
[TD="width: 38, bgcolor: transparent"]Sun
[/TD]
[TD="width: 41, bgcolor: transparent"]Mon
[/TD]
[TD="width: 37, bgcolor: transparent"]Tue
[/TD]
[TD="width: 42, bgcolor: transparent"]Wed
[/TD]
[TD="width: 63, bgcolor: transparent"]Thu
[/TD]
[TD="width: 37, bgcolor: transparent"]Fri
[/TD]
[TD="width: 38, bgcolor: transparent"]Sat
[/TD]
[TD="width: 37, bgcolor: transparent"]Sun
[/TD]
[TD="width: 41, bgcolor: transparent"]Mon
[/TD]
[TD="width: 36, bgcolor: transparent"]Tue
[/TD]
[TD="width: 72, bgcolor: transparent"]Wed
[/TD]
[TD="width: 36, bgcolor: transparent"]Thu
[/TD]
[TD="width: 27, bgcolor: transparent"]Fri
[/TD]
[TD="width: 34, bgcolor: transparent"]Sat
[/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"]List
[/TD]
[TD="width: 53, bgcolor: transparent"]Times
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]Employee1
[/TD]
[TD="width: 38, bgcolor: transparent"]x
[/TD]
[TD="width: 41, bgcolor: transparent"]x
[/TD]
[TD="width: 37, bgcolor: transparent"]L11
[/TD]
[TD="width: 42, bgcolor: transparent"]SP
[/TD]
[TD="width: 63, bgcolor: transparent"]training
[/TD]
[TD="width: 37, bgcolor: transparent"]L12
[/TD]
[TD="width: 38, bgcolor: transparent"]L13
[/TD]
[TD="width: 37, bgcolor: transparent"][/TD]
[TD="width: 41, bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: transparent"]x
[/TD]
[TD="width: 36, bgcolor: transparent"]x
[/TD]
[TD="width: 27, bgcolor: transparent"][/TD]
[TD="width: 34, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"]L10
[/TD]
[TD="width: 53, bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]Employee2
[/TD]
[TD="width: 38, bgcolor: transparent"]L11
[/TD]
[TD="width: 41, bgcolor: transparent"]x
[/TD]
[TD="width: 37, bgcolor: transparent"]x
[/TD]
[TD="width: 42, bgcolor: transparent"]L20
[/TD]
[TD="width: 63, bgcolor: transparent"]L13
[/TD]
[TD="width: 37, bgcolor: transparent"]L12
[/TD]
[TD="width: 38, bgcolor: transparent"]SP
[/TD]
[TD="width: 37, bgcolor: transparent"]x
[/TD]
[TD="width: 41, bgcolor: transparent"]x
[/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 27, bgcolor: transparent"][/TD]
[TD="width: 34, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"]L11
[/TD]
[TD="width: 53, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]Employee3
[/TD]
[TD="width: 38, bgcolor: transparent"]L12
[/TD]
[TD="width: 41, bgcolor: transparent"]TRA
[/TD]
[TD="width: 37, bgcolor: transparent"]L13
[/TD]
[TD="width: 42, bgcolor: transparent"]L11
[/TD]
[TD="width: 63, bgcolor: transparent"]x
[/TD]
[TD="width: 37, bgcolor: transparent"]x
[/TD]
[TD="width: 38, bgcolor: transparent"]L14
[/TD]
[TD="width: 37, bgcolor: transparent"][/TD]
[TD="width: 41, bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"]x
[/TD]
[TD="width: 72, bgcolor: transparent"]x
[/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 27, bgcolor: transparent"][/TD]
[TD="width: 34, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"]L12
[/TD]
[TD="width: 53, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]Employee4
[/TD]
[TD="width: 38, bgcolor: transparent"]L12
[/TD]
[TD="width: 41, bgcolor: transparent"]L20
[/TD]
[TD="width: 37, bgcolor: transparent"]L14
[/TD]
[TD="width: 42, bgcolor: transparent"]L13
[/TD]
[TD="width: 63, bgcolor: transparent"]L15
[/TD]
[TD="width: 37, bgcolor: transparent"]x
[/TD]
[TD="width: 38, bgcolor: transparent"]x
[/TD]
[TD="width: 37, bgcolor: transparent"][/TD]
[TD="width: 41, bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"]x
[/TD]
[TD="width: 27, bgcolor: transparent"]x
[/TD]
[TD="width: 34, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"]L13
[/TD]
[TD="width: 53, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]Employee5
[/TD]
[TD="width: 38, bgcolor: transparent"]L13
[/TD]
[TD="width: 41, bgcolor: transparent"]L15
[/TD]
[TD="width: 37, bgcolor: transparent"]L12
[/TD]
[TD="width: 42, bgcolor: transparent"]x
[/TD]
[TD="width: 63, bgcolor: transparent"]x
[/TD]
[TD="width: 37, bgcolor: transparent"]L20
[/TD]
[TD="width: 38, bgcolor: transparent"]WK
[/TD]
[TD="width: 37, bgcolor: transparent"][/TD]
[TD="width: 41, bgcolor: transparent"]x
[/TD]
[TD="width: 36, bgcolor: transparent"]x
[/TD]
[TD="width: 72, bgcolor: transparent"]vacation
[/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 27, bgcolor: transparent"][/TD]
[TD="width: 34, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"]L14
[/TD]
[TD="width: 53, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]Employee6
[/TD]
[TD="width: 38, bgcolor: transparent"]L14
[/TD]
[TD="width: 41, bgcolor: transparent"]x
[/TD]
[TD="width: 37, bgcolor: transparent"]x
[/TD]
[TD="width: 42, bgcolor: transparent"]L12
[/TD]
[TD="width: 63, bgcolor: transparent"]L20
[/TD]
[TD="width: 37, bgcolor: transparent"]WK
[/TD]
[TD="width: 38, bgcolor: transparent"]TRA
[/TD]
[TD="width: 37, bgcolor: transparent"][/TD]
[TD="width: 41, bgcolor: transparent"]x
[/TD]
[TD="width: 36, bgcolor: transparent"]x
[/TD]
[TD="width: 72, bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 27, bgcolor: transparent"][/TD]
[TD="width: 34, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"]L15
[/TD]
[TD="width: 53, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]Employee7
[/TD]
[TD="width: 38, bgcolor: transparent"]L15
[/TD]
[TD="width: 41, bgcolor: transparent"]L16
[/TD]
[TD="width: 37, bgcolor: transparent"]x
[/TD]
[TD="width: 42, bgcolor: transparent"]x
[/TD]
[TD="width: 63, bgcolor: transparent"]leave
[/TD]
[TD="width: 37, bgcolor: transparent"]L13
[/TD]
[TD="width: 38, bgcolor: transparent"]L12
[/TD]
[TD="width: 37, bgcolor: transparent"][/TD]
[TD="width: 41, bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: transparent"]x
[/TD]
[TD="width: 36, bgcolor: transparent"]x
[/TD]
[TD="width: 27, bgcolor: transparent"][/TD]
[TD="width: 34, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"]L16
[/TD]
[TD="width: 53, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]Employee8
[/TD]
[TD="width: 38, bgcolor: transparent"]x
[/TD]
[TD="width: 41, bgcolor: transparent"]x
[/TD]
[TD="width: 37, bgcolor: transparent"]L16
[/TD]
[TD="width: 42, bgcolor: transparent"]L12
[/TD]
[TD="width: 63, bgcolor: transparent"]L13
[/TD]
[TD="width: 37, bgcolor: transparent"]L14
[/TD]
[TD="width: 38, bgcolor: transparent"]L20
[/TD]
[TD="width: 37, bgcolor: transparent"]x
[/TD]
[TD="width: 41, bgcolor: transparent"]x
[/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 27, bgcolor: transparent"][/TD]
[TD="width: 34, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"]L17
[/TD]
[TD="width: 53, bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]Employee9
[/TD]
[TD="width: 38, bgcolor: transparent"]L16
[/TD]
[TD="width: 41, bgcolor: transparent"]L14
[/TD]
[TD="width: 37, bgcolor: transparent"]x
[/TD]
[TD="width: 42, bgcolor: transparent"]x
[/TD]
[TD="width: 63, bgcolor: transparent"]L12
[/TD]
[TD="width: 37, bgcolor: transparent"]L15
[/TD]
[TD="width: 38, bgcolor: transparent"]L12
[/TD]
[TD="width: 37, bgcolor: transparent"][/TD]
[TD="width: 41, bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"]x
[/TD]
[TD="width: 72, bgcolor: transparent"]x
[/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 27, bgcolor: transparent"][/TD]
[TD="width: 34, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"]L18
[/TD]
[TD="width: 53, bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]Employee10
[/TD]
[TD="width: 38, bgcolor: transparent"]L20
[/TD]
[TD="width: 41, bgcolor: transparent"]L13
[/TD]
[TD="width: 37, bgcolor: transparent"]L12
[/TD]
[TD="width: 42, bgcolor: transparent"]x
[/TD]
[TD="width: 63, bgcolor: transparent"]x
[/TD]
[TD="width: 37, bgcolor: transparent"]SP
[/TD]
[TD="width: 38, bgcolor: transparent"]L11
[/TD]
[TD="width: 37, bgcolor: transparent"]x
[/TD]
[TD="width: 41, bgcolor: transparent"]x
[/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 27, bgcolor: transparent"][/TD]
[TD="width: 34, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"]L19
[/TD]
[TD="width: 53, bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]Employee11
[/TD]
[TD="width: 38, bgcolor: transparent"]x
[/TD]
[TD="width: 41, bgcolor: transparent"]x
[/TD]
[TD="width: 37, bgcolor: transparent"]L15
[/TD]
[TD="width: 42, bgcolor: transparent"]WK
[/TD]
[TD="width: 63, bgcolor: transparent"]L12
[/TD]
[TD="width: 37, bgcolor: transparent"]L11
[/TD]
[TD="width: 38, bgcolor: transparent"]x
[/TD]
[TD="width: 37, bgcolor: transparent"]x
[/TD]
[TD="width: 41, bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 27, bgcolor: transparent"][/TD]
[TD="width: 34, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"]L20
[/TD]
[TD="width: 53, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]Employee12
[/TD]
[TD="width: 38, bgcolor: transparent"]TRA
[/TD]
[TD="width: 41, bgcolor: transparent"]L12
[/TD]
[TD="width: 37, bgcolor: transparent"]x
[/TD]
[TD="width: 42, bgcolor: transparent"]x
[/TD]
[TD="width: 63, bgcolor: transparent"]L14
[/TD]
[TD="width: 37, bgcolor: transparent"]L16
[/TD]
[TD="width: 38, bgcolor: transparent"]L15
[/TD]
[TD="width: 37, bgcolor: transparent"][/TD]
[TD="width: 41, bgcolor: transparent"]x
[/TD]
[TD="width: 36, bgcolor: transparent"]x
[/TD]
[TD="width: 72, bgcolor: transparent"]testing
[/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 27, bgcolor: transparent"][/TD]
[TD="width: 34, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"]TRA
[/TD]
[TD="width: 53, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]Employee13
[/TD]
[TD="width: 38, bgcolor: transparent"]WK
[/TD]
[TD="width: 41, bgcolor: transparent"]L12
[/TD]
[TD="width: 37, bgcolor: transparent"]L20
[/TD]
[TD="width: 42, bgcolor: transparent"]x
[/TD]
[TD="width: 63, bgcolor: transparent"]x
[/TD]
[TD="width: 37, bgcolor: transparent"]L14
[/TD]
[TD="width: 38, bgcolor: transparent"]L16
[/TD]
[TD="width: 37, bgcolor: transparent"]x
[/TD]
[TD="width: 41, bgcolor: transparent"]x
[/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 27, bgcolor: transparent"][/TD]
[TD="width: 34, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"]WK
[/TD]
[TD="width: 53, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]Employee14
[/TD]
[TD="width: 38, bgcolor: transparent"]SP
[/TD]
[TD="width: 41, bgcolor: transparent"]L11
[/TD]
[TD="width: 37, bgcolor: transparent"]WK
[/TD]
[TD="width: 42, bgcolor: transparent"]TRA
[/TD]
[TD="width: 63, bgcolor: transparent"]L11
[/TD]
[TD="width: 37, bgcolor: transparent"]x
[/TD]
[TD="width: 38, bgcolor: transparent"]x
[/TD]
[TD="width: 37, bgcolor: transparent"][/TD]
[TD="width: 41, bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 27, bgcolor: transparent"][/TD]
[TD="width: 34, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"]SP
[/TD]
[TD="width: 53, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thanks for the extra information & table.

peerogel said:
I did one week and I was already getting repetitions.
Click to expand...
Can you clarify what you mean by "repetitions" in relation to your table. Are there examples in that table you can point me to?
- Do you mean a List item occurs more than the required times on a day (eg L13 occurs more than once on Thu?)
- Or do you mean Employee9 has L12 listed more than once in their row?
- Something else?
 
Upvote 0
I do shift work and we have these sorts of problems. This looks like a military and/or utility worker schedule.

First thing I think you need to do is back up and show your table with one week and 4 employees. This simplification may help people understand what you're trying to get at. I can't tell.

Do you need each employee to achieve a random selection or do you need each workday (or shift) to achieve a random selection? You're on the right board - you just need to rephrase your question better.

What are all the manual entries vs. the entries which are being randomly generated?
 
Last edited:
Upvote 0
It's hard to show using only the tables. Is it ok to use google docs in this forum?

But yes I was refereeing to the row
- Or do you mean Employee9 has L12 listed more than once in their row?

My goal is to use the list of assignments and assign it ramdomly to an employee for the day (down the column) and try not to give him the same assignment for two weeks (across the row). Currently I do them manually (it takes for ever) and I do get repetitions (across the row) but I try to keep them to a minimum. Photomofo you are correct, it is a worker schedule.

In the table example I used twelve assignments distributed randomly to the 14 employees (down the column) for Sunday.
For Monday using the same twelve assignments, I redistributed them down the column avoiding repeating the assignment I used Sunday.
For Tuesday using the same twelve assignments, I redistributed them down the column avoiding repeating the assignment I used Sunday and Monday.
For Wednesday using the same twelve assignments, I redistributed them down the column avoiding repeating the assignment I used Sunday, Monday and Tuesday.​
and so on until I had to repeat L12 for employee9.

Thanks once more. ​
 
Upvote 0
This should get you close. The Days are arranged vertically vs. Horizontally in your table.

Code: 
Option Explicit


Sub Schedule()


Dim i As Integer
Dim Employee As Integer
Dim Task As Integer
Dim Day_x As Integer
Dim Schedule As Variant
Dim Task_Count As Integer
Dim Loop_Count As Integer
Dim Test1 As Boolean


ThisWorkbook.Save


Schedule = Sheets("Schedule").Range("A1:M1000")


For Employee = 2 To 13


    For Day_x = 2 To 15
    
       'Test for Manual entries
       If Schedule(Day_x, Employee) = "" Then
                   
            Test1 = False
            Loop_Count = 0
                        
            Do While Test1 = False
                                  
                Test1 = True
                
                'Assign a Task Number Randomly
                'Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)
                
                Schedule(Day_x, Employee) = Int((12 - 1 + 1) * Rnd + 1)


                'This Loop Checks for Task coverage overlap


                For i = 2 To 13
                
                    If i = Employee Then
                    
                        'Do Nothing
                        
                    Else
                    
                        If Schedule(Day_x, i) = Schedule(Day_x, Employee) Then
                        
                            Test1 = False
                            
                        End If
                                     
                    End If
                
                Next i
                
                'This Loop Checks for Task overlap
                
                If Test1 = True Then
                
                    Task_Count = 0
                
                    For i = 2 To 15
                    
                        If Schedule(i, Employee) = Schedule(Day_x, Employee) Then
                            
                            Task_Count = Task_Count + 1
                                         
                        End If
                    
                    Next i
                    
                    If Task_Count > 1 Then
                    
                        Test1 = False
                    
                    End If
                
                End If
                
                Loop_Count = Loop_Count + 1
                
                If Loop_Count > 10000 Then
                
                    Test1 = True
                    
                End If
                                        
            Loop


       End If
    
    Next Day_x


Next Employee


Sheets("Schedule").Range("A1:M1000") = Schedule


End Sub
 
Last edited:
Upvote 0
Hmmm... On second thought... This isn't quite working.

I tweaked the Loop_Count gate up. That seems to have minimized the odds of getting assigned the same task three times in 2 weeks.

Hmmm... The code needs a bit of work but looks close.
 
Last edited:
Upvote 0
Any random scheme will always be "unfair" to some employee.

If they have preferences, why not instead have them rank-order their preferred assignments, and then use code that essentially allows them to pick in round-robin fashion?
 
Upvote 0
This is closer. This correctly fills out an empty schedule of 12 employees and 12 tasks. It will also fills in a schedule with Manual entries but in this case it won't be able to assign all tasks. You'll need to tweak this.

Code: 
Option Explicit


Sub Schedule()


Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Employee As Integer
Dim Task As Integer
Dim Day_x As Integer
Dim Schedule As Variant
Dim Task_Count As Integer
Dim Loop_Count As Integer
Dim Test1 As Boolean


ThisWorkbook.Save


Schedule = Sheets("Schedule").Range("A1:M1000")


For Employee = 2 To 13


    For Day_x = 2 To 15
    
       'Test for Manual entries
       If Schedule(Day_x, Employee) = "" Then
                   
            Test1 = False
            Loop_Count = 0
                        
            Do While Test1 = False
                                  
                Test1 = True
                
                'Assign a Task Number Randomly
                'Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)
                
                Schedule(Day_x, Employee) = Int((12 - 1 + 1) * Rnd + 1)


                'This Loop Checks for Task coverage overlap


                For i = 2 To 13
                
                    If i = Employee Then
                    
                        'Do Nothing
                        
                    Else
                    
                        If Schedule(Day_x, i) = Schedule(Day_x, Employee) Then
                        
                            Test1 = False
                            
                        End If
                                     
                    End If
                
                Next i
                
                'This Loop Checks for Task overlap
                
                If Test1 = True Then
                
                    Task_Count = 0
                
                    For i = 2 To 15
                    
                        If Schedule(i, Employee) = Schedule(Day_x, Employee) Then
                            
                            Task_Count = Task_Count + 1
                                         
                        End If
                    
                    Next i
                    
                    If Task_Count > 1 Then
                    
                        Test1 = False
                    
                    End If
                
                End If
                
                Loop_Count = Loop_Count + 1
                
                If Loop_Count > 10000 Then
                
                    Test1 = True
                    
                End If
                                        
            Loop


       End If
    
    Next Day_x


Next Employee


'Verify task coverage


For Day_x = 2 To 15


    For Task = 1 To 12
    
        Test1 = False
    
        For j = 2 To 13
        
            If Schedule(Day_x, j) = Task Then
            
                Test1 = True
                
            End If
    
        Next j
    
        If Test1 = False Then
             
            'Task X not performed
             
            'Find out which task was double performed
            
            For j = 1 To 12
        
                Task_Count = 0
        
                For i = 2 To 13
                
                    If Schedule(Day_x, i) = j Then
                        
                        Task_Count = Task_Count + 1
                                     
                    End If
                
                Next i
                
                If Task_Count > 1 Then
                
                    For k = 2 To 13
                    
                        If Schedule(Day_x, k) = j Then
                        
                            Schedule(Day_x, k) = Task
                            Exit For
                        
                        End If
                        
                    Next k
                
                End If
                
            
            Next j
        
        End If
        
    Next Task


Next Day_x


Sheets("Schedule").Range("A1:M1000") = Schedule


End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Filter on decremental list
Replies
5
Views
121
Peter_SSs
Peter_SSs
K
  • Question Question
Trying to make a formula to figure out what total hours in the month will be, based on what I already know
Replies
1
Views
50
Sufiyan97
Sufiyan97
A
  • Question Question
VBA help - Locking & unlocking on certain times of day
2
Replies
17
Views
468
dmt32
D
F
  • Solved
How to change value from data validation based on value in col A?
Replies
5
Views
335
Eric W
Eric W
Sandeep Warrier
  • Question Question
Calculate No. of Working Days based on Shift Pattern
Replies
2
Views
148
Tetra201
Tetra201

Forum statistics

Threads
1,224,812
Messages
6,181,104
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top