I previously received the following formula from pgc to give me a column of random numbers from 1 to 99 without duplicates and it worked great:
SMALL(IF(COUNTIF($A$1:A1,ROW($1:$99))<>1,ROW($1:$99)),1+INT(RAND()*(99-ROW()+ROW($B$2))))
Holding CTRL+SHIFT+ENTER since it is an array
After putting it to use I began to wonder if it could be tweaked so that the random number would only appear if the cell to the right was not blank. I haven't been able to make anything work. Any ideas?
SMALL(IF(COUNTIF($A$1:A1,ROW($1:$99))<>1,ROW($1:$99)),1+INT(RAND()*(99-ROW()+ROW($B$2))))
Holding CTRL+SHIFT+ENTER since it is an array
After putting it to use I began to wonder if it could be tweaked so that the random number would only appear if the cell to the right was not blank. I haven't been able to make anything work. Any ideas?