# Random numbers - an added twist

#### secondtry

##### New Member
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?

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### barry houdini

##### MrExcel MVP
Try this in A2 copied down, should give a blank if cell to the right (in column B) is blank

=IF(B2="",SMALL(IF(COUNTIF(\$A\$1:A1,ROW(\$1:\$99))<>1,ROW(\$1:\$99)),1+INT(RAND()*(99-ROW()+COUNTIF(\$A\$1:A1,"")+ROW(\$A\$2)))),"")

confirmed with CTRL+SHIFT+ENTER

note: A1 should not be blank

#### secondtry

##### New Member
That worked like a charm. Within the formula, can I also change the range of random numbers that I want to appear? That is to say, if I want to apply the same formula but have the option of altering the range of numbers anywhere between 1 and 99, what can I do? When I tried replacing the "99" as demonstrated below, I still had random numbers of greater than the chosen variable appearing.

Original formula:
IF(C5="","",SMALL(IF(COUNTIF(\$B\$4:B4,ROW(\$4:\$102))<>1,ROW(\$4:\$102)),1+INT(RAND()*(99-ROW()+COUNTIF(\$B\$4:B4,"")+ROW(\$B\$5)))))

#### barry houdini

##### MrExcel MVP
you need to replace all three 99s, you haven't changed the last one

Replies
17
Views
666
Replies
7
Views
610
Replies
5
Views
508
Replies
2
Views
235
Replies
6
Views
158

1,171,203
Messages
5,874,322
Members
433,043
Latest member
CvD

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back