Generate equal number of numbers between interval-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

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)))