• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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

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

Top