Unique random numbers in array ?

Question

Hi Folks

I have a column A rows 2-7 in each row i want a randomised number from 1-6 but i want each number to appear uniquely, so no duplicates in the 6 cells, can this be done, ive tried random functions but although i get random numbers between 1 and 6 i still get duplicates any help appreciated tx

Answer 1

A #SPILL! error occurs when you use an array formula (any formula that produces an array of values as it's result) and one or more cells into which that result is to be placed contains pre-existing formulas or values.

PopulationSize is the number of random values to produce. Start is the first or smallest value in the population and Step is the increment between population values. So a Start of 10 and a Step of 5 will produce a population containing 10, 15, 20, 25....

Kevin

Answer 2

Hi Kevin it works tx there was an extra comma, works great many thanks

Answer 3

Hi Kevin

That gives me a #spill, sorry am i being thick here do i replace pop size with number of rows or the range ?

best colin

Answer 4

Use this formula in cell A2:

=LET(PopulationSize,6,Start,1,Step,1,SORTBY(SEQUENCE(PopulationSize,,Start,Step),RANDARRAY(PopulationSize)))

Kevin