CODEWITHSUNDEEP
excelrandom
Joe
Joe

Reputation: 395

Generate equal number of numbers between interval

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!

Upvotes: 0

Views: 134

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

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

enter image description here

Upvotes: 1

KekuSemau
KekuSemau

Reputation: 6856

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

Upvotes: 3

Related Questions