Unique random numbers

[Adell]

I require unique random numbers to be generated per the number of contestants on drawsheets. The array formula I have is eg. for 20 competitors =LARGE(ROW($1:$20)*NOT(COUNTIF($B$2:B2,ROW($1:$20))),RANDBETWEEN(1,21-ROW(A1))). For 10 competitors, I have to manually change the ROW formula to ROW ($1:$20) and the random selection to RANDBETWEEN (1,11-ROW(a1). How can improve this formula to automatically use the number of contestants per draw sheet to adjust the ROW maximum and RANDBETWEEN formula?

 

[Hui]

Adcell


I can't even get your formula to work as you described

It is probably because I'm pasting into the wrong range?


You should be able to modify it as below, using the Indirect() function

=LARGE(ROW(INDIRECT("$1:$"&n)) * NOT(COUNTIF($B$2:B2, ROW(INDIRECT("$1:$"&n)))), RANDBETWEEN(1, n+1-ROW(A1)))

Where n can be any cell address say ZZ1

 

[NARAYANK991]

Hi ,


That's a wonderful formula ! To parameterize it to the number of contestants , let us assume your number is in cell D1 ; enter the following formula in B3 , and copy downwards. The formula is to be entered as an array formula , using CTRL SHIFT ENTER :


=LARGE(ROW(INDIRECT("1:"&$D$1))*NOT(COUNTIF($B$2:B2,ROW(INDIRECT("1:"&$D$1)))),RANDBETWEEN(1,$D$1+1-ROW(A1)))


If you enter it as far as you want , the relevant cells will contain the unique random numbers , while the remaining cells will contain #NUM!. Ensure that B2 does not contain a number which is within the required range.


Narayan

 

[Adell]

Thank you, it works like a charm. My formula, as indicated initially, is entered in B3 and copied down several rows. I have added the IFERROR part as well now, and now it also looks "wonderful"

 

[SirJB7]

Hi, Adell!


Very nice formula indeed. I ever failed trying to generate lists of unique random numbers.

Chapeau, lady!


Regards!