excel - Microsoft Community

hi,

i trying create list of random numbers without duplication,  if list start in b5 , runs b12 have written following formula in e.g.-  cell b7              {=if(rand()=($b$5:b6),b7,rand())}  

the formula find if 1 of cells above b7 has same value , run rand() function once again, however, doesn't work since check done once , in rare instance second rand() returns value exists in list above wont catch (i need integers rounding amplifies disadvantage), have solution this? thx, david 

enter below formula array formula (ctrl+shift+enter) in cell b5 & copy down:

=large(row($1:$247)*not(countif(b$4:b4, row($1:$247))), randbetween(1,251-row(b4)))

this return random numbers in range of 1 247.

regards,

amit tandon

www.globaliconnect.com

if response answers question please mark answer.

Office / Excel / Other/unknown / Office 2013