how to go about generating a column of numbers between a min and max value (inclusive), distributed equal number of times?
For example, generate integers between 1 and 5 in 600 cells in excel. The column will consist of 120 1's, 120 2's....and 120 5's in random order.
Any guidance is appreciated!
You could build the bucket of all numbers (e.g. 1 to 5 each 120 times) and then draw with deletion in random order:
Sub test()
Dim c As Collection
Set c = GenerateBucket(1, 5, 120)
Dim row As Long
Dim index As Long
row = 1
' now draw with deletion in random order
Randomize
While c.Count > 0
' generate a number [1..c.count]
index = Int(Rnd() * c.Count + 1)
Cells(row, 1).Value = c(index)
c.Remove index
row = row + 1
Wend
End Sub
Private Function GenerateBucket(xmin As Long, xmax As Long, times As Long) As Collection
Dim i As Long, n As Long
Dim rv As New Collection
For i = xmin To xmax
For n = 1 To times
rv.Add i
Next
Next
Set GenerateBucket = rv
End Function
If you have access to the more recent functions in Excel 365, you can also do it like this:
=LET(min,1,
max,5,
times,120,
range,max-min+1,
total,range*times,
colArr,SEQUENCE(range,1,min),
rowArr,SEQUENCE(1,times,1,0),
rectArr,colArr*rowArr,
finalArr,TOCOL(rectArr),
SORTBY(finalArr,RANDARRAY(total)))
