RAND number function

[druid9855]

I can only seem to get the hang of the simplest random number functions. I have a grid consisting of 315 squares. I need a random spread of the numbers 1-21, each repeated (also randomly) 15 times to put an integer in each cell.

03|12|09|08|01|08|10
04|05|05|11|14|08|04
02|08|12|09|03|15|14

Each integer must be used 15 times exactly, as well as all the integers from 1-21. BTW, it doesn't even necessarily have to be in a grid; linearly is fine.

Thanks for your help.

 

[Akashwani]

Hi,

Take a look here........

McGimpsey & Associates : Excel : Random Integers without repetition

I hope that helps.

Ak

 

[druid9855]

Thank you for your response, AK, it is appreciated. And, because I do not believe that Excel is capable of giving me 21 randomized groups of 15 repeated integers, I think the best way is to expand the randomized range from 21 to 315 (21*15) and then assigning each number above 21 a value equivalent to its place in the original 21. Therefore:

1=22=43=64=85...=295
2=23=44=65=86...=296, etc.

As they say- "Duh!"

Thanks again!

 

[northwolves]

A subroutine works for you:

Sub test()
Application.ScreenUpdating = False
[a1:o21] = [row(1:21)]
[p1:p21] = "=rand()"
For i = 0 To 59
[a1:p21].Offset(0, i Mod 15).Sort [p1]
Next
[p:p] = ""
Application.ScreenUpdating = True
End Sub

 

[druid9855]

Fantastic! Thank you very much!