# Random NUM Generator Help

#### Brew

##### Well-known Member
How do you create a random number generator formula or VBA that generates the 120 random num values in cells Z1-Z120 from the list in cells E1-E1000, after comparing the num values in cells E1-E1000 to any num values listed in cells Y1-Y12.
Conditions:
The cells in column E uses the following formula type to auto generate it’s numerical values
Example:
E1=SMALL(A1:C1,1)&SMALL(A1:C1,2)&SMALL(A1:C1,3)
E2=SMALL(A2:C2,1)&SMALL(A2:C2,2)&SMALL(A2:C2,3)
And so on …….
E1000=SMALL(A1000:C1000,1)&SMALL(A1000:C1000,2)&SMALL(A1000:C1000,3)

Also the numerical values in cells Y1-Y12 are entered manually.
Thank you

Brew

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
How about Z1 =OFFSET(\$E\$1,RANDBETWEEN(0,999),0) and copy down through Z120?

Not sure what you mean by "after comparing the num values in cells E1-E1000 to any num values listed in cells Y1-Y12". What exactly do you want to do with this comparison?

Let me clarify:
I would like cells Z1-Z120 to generate 120 most likely random 3 digit numbers from the 3 digit combinations in the Y column list from Y1-Y12. The combinations in the Y column are 3 digits, between 000-999, manually entered and may not have values in all 12 cells (Y1-Y12). Therefore, the random results should only compare the actual values in the list and ignore any blank cells in Y1-Y12. It will not exceed 12 values.

Example1:
Y1=063
Y2=684
Y3=228
Y4=051
Ans. Compare Y1 through Y4 to get 120 most likely 3 digit combinations in cells Z1-Z120

Example2:
Y1=063
Y2=684
Y3=228
Y4=051
Y5=872
Y6=024
Ans. Compare Y1 through Y6 to get 120 most likely 3 digit combinations in cells Z1-Z120

I think it's the same idea:

Z1=OFFSET(\$Y\$1,RANDBETWEEN(0,COUNTA(Y:Y)-1),0)

What does "most likely random" mean? If it's "most likely", it's not random

Ok Thanks Oaktree, I mean most likely. but I type the following into Z1, then what do I place in Z2 and so on:
Z1=OFFSET(\$Y\$1,RANDBETWEEN(0,COUNTA(Y:Y)-1),0)

Also, how do I execute, to get the results after I enter
the values in the y column?

Replies
2
Views
842
Replies
6
Views
798
Replies
4
Views
546
Replies
15
Views
774
Replies
3
Views
627

1,219,696
Messages
6,149,778
Members
450,913
Latest member
mdsuther

### 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