0

New Bei - First line >is dates, second is days and the third is week number.

This is how my data look like. it is a weekly off register wherein I need to add word "WO" randomly in only 2 cells only for each employee within a week dates range and so on to every week dates range in a month. blank area is where I need this working.

Sub Rando()
      Lr = Cells(Rows.Count, 2).End(xlUp).Row
   Lc = Cells(8, Columns.Count).End(xlToLeft).Column

For j = 8 To Lc
For i = 9 To Lr


Randomize


ActiveSheet.Cells(i, j).Value = "Wo"


Next i
Next j


End Sub
Ajit Kumar
  • 3

1 Answers1

0

I don't even need VBA for this. You can have excel generate a single random number for each week for each person. That number must provide a unique mapping so that say 0 means {monday, tuesday}, 1 means {monday, wednesday}, ..., 6 mean {monday, saturday}, 7 means {tuesday, monday}, 8 means {tuesday, wednesday} (because you can't have the same day twice), and so forth.

If you have to pick two days out of seven, in any order, you will have 7P2 (permutation) = 42 options in total. Technically {monday, tuesday} is the same as {tuesday, monday} so it should be 7C2 (combination) = 21 options but in order to easily convert (with mod and div functions) between a random number 0..41, and its matching set of two days, it is just easier to use permutations. If we assign a number 1..7 to the days of the week, the full mapping looks like this:

enter image description here

You can use a VLOOKUP on the above table to return the random days e.g.

=VLOOKUP(RANDBETWEEN(0,41), A2:C43, {2,3}, FALSE)        ...CTRL+SHIFT+ENTER for array formula in version prior to Excel 365

Or use the following three formula, say in cells A2, B2 and C2:

=RANDBETWEEN(0,41)
=INT((A2)/6)+1
=IF((MOD(A2,6)+1)<B2,MOD(A2,6)+1,MOD(A2,6)+2)

To determine if you should write a "WO" on the day, use something like this in cell E2 (and copy-paste F2 through K2):

=IF(OR(COLUMN(A$1)=$B2, COLUMN(A$1)=$C2),"WO","")

PS. If you don't want e.g. two WO's to be on consecutive days or say {monday, friday}, the lookup table might work easiest. Just delete the undesirable pairs and reduce the max RANDBETWEEN number accordingly.

ExcelEverything
  • 3,194