# Random number

#### NICKI CROWELL

##### New Member
Hi
I need to generate random numbers 1-15 that can't repeat within every set of 5 and I need 6000 rows of these.
Is this possible?
Thanks

#### p45cal

##### Well-Known Member
In the attached, change the number in cell A2 from 50 to 6000, right-click on cell C1 and choose Refresh.
Every block of 5 rows will not contain repeats.
Copy, then Paste|Values elsewhere.

#### Attachments

• 17.6 KB Views: 8

#### NICKI CROWELL

##### New Member
Wow Thanks!
I'm looking and kind of see what you did. Seems like you are creating sets of tables that are 5 cells and stringing them together. I don't understand completely though.
We may be doing this job repeatedly using random numbers other than 1-15 or different sets other that in 5.
Is there a way for me to change those values?

#### p45cal

##### Well-Known Member
In the attached, table on left is as for your msg#1 (well, 60 instead of 6000).
Make sure From is always smaller then To
Count
is <= difference between From and To
It only deals in whole numbers.
What it does:
1. Works out how many mini-tables are needed = Rows/Count
2. Creates a minitable in each row by:
• Creating a mini-table with the necessary number of rows (difference between From and To) with all the values from From to To in.
• Then it adds a random number column to all those rows.
• Sorts that mini-table by the random number column.
• Takes only the top Count rows of that mini-table.
3. Expands all those mini-tables and removes all the other columns.

#### Attachments

• 18.6 KB Views: 5

#### Peter Bartholomew

##### Well-Known Member
The Excel365 formula that I have added builds a repeating block of the digits 1-15 and a set of random numbers for each block. Sorting shuffles the digits within their block and I finish by returning the first 5 digits for each block.

#### Attachments

• 21.6 KB Views: 5

#### Peter Bartholomew

##### Well-Known Member
@p45cal There is substantial commonality despite our selecting different tool sets.

#### NICKI CROWELL

##### New Member
Thank! I seem to be getting errors in the second file. My version is older than yours. Could be a compatibility issue.

#### p45cal

##### Well-Known Member
Thank! I seem to be getting errors in the second file. My version is older than yours. Could be a compatibility issue.
Who are you talking to?