# RAND number function

#### druid9855

##### New Member
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.

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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!

A subroutine works for you:

Code:
``````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``````

Fantastic! Thank you very much!

Replies
3
Views
118
Replies
4
Views
237
Replies
6
Views
260
Replies
2
Views
342
Replies
5
Views
464

1,206,754
Messages
6,074,750
Members
446,083
Latest member
kfunt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back