How to generate a random number list from 1 to 10 with no dupes or volatile functions

[JulianS96]

Hi forum!

Just wondering how to generate a random order list between 1 and 10, with no dupes or functions that will result in the order being randomized again anytime the spreadsheet is loaded/changed.

I have attached an example of what I want to achieve below.

[MrShorty]

Here's how I would usually do something like this:

1) Generate 10 unique random numbers in 10 convenient cells (say A1:A10, for example). =RAND()
2) Rank the numbers to get the integers 1 to 10 in random order B1 =RANK(A1,$A$1:$A$10) copied/pasted/filled into B2:B10.
3) Select B1:B10 -> copy -> Select C1 -> Paste special as values.

A1:B10 will update with each calculate event, constantly generating random lists. Whenever I need a new randomization, I can copy/paste as values from column B into column C.

Will something like that work for you?

[JulianS96]

Can this be done without a helper column, negating the need for the needless 0.xxxxx random numbers generated between 0 and 1?

And can this also be done without volatile functions like rand() which change on every sheet calculation?

[MrShorty]

I'm usually optimistic that anything is possible with enough time, effort, and ingenuity. If you help us understand all of your requirements, I'm confident we can help you come up with something.

For something like this, the easiest approach that I see to getting rid of the volatile spreadsheet columns is to move the random number generation to another programming language and use the spreadsheet as the output container (so the spreadsheet is not actually generating the random numbers). Traditionally, Excel users have defaulted to VBA for this sort of thing. Recently, I have seen some rumblings that Microsoft is trying to push Office users towards Javascript as an alternate programming language. Basically, you choose the programming language you want to use, program it to perform the same steps (generate 10 random unique numbers and rank them), then send the output to the spreadsheet. Are you allowed to use "macros" written in another programming language for this, or are you required to use native Excel functions?

[Bo_Ry]

Please try at C2

=AGGREGATE(15,6,ROW(C$1:C$10)/ISNA(MATCH(ROW(C$1:C$10),C$1:C1,)),RANDBETWEEN(1,ROWS(C$1:C$10)-ROWS(C$2:C2)+1))

RAND and RANDBETWEEN are volatile functions.
But the random can be stop by set calcuation opiton to manual and Press F9 to Random

Or Enable Iteretive calculation and use circular reference.
E2
=IF($A$1=1,E2,AGGREGATE(15,6,ROW(E$1:E$10)/ISNA(MATCH(ROW(E$1:E$10),E$1:E1,)),RANDBETWEEN(1,ROWS(E$1:E$10)-ROWS(E$2:E2)+1)))

Stop random by key 1 in A1, delete 1 in A1 to random again

[torachan]

Small macro fires on Workbook_Open event.
Each time the workbook is opened a random set of numbers will appear in column A.
No on-sheet formula or helper columns.
torachan

[JulianS96]

Hi all thanks for your solutions. I will try them out now!

[JulianS96]

Small macro fires on Workbook_Open event.
Each time the workbook is opened a random set of numbers will appear in column A.
No on-sheet formula or helper columns.
torachan

I'm getting
"Run-time error '1004':
Method 'Range' of object '_Global' failed"