Random numbers

M

Mike

manipulating the numbers by eliminating duplicates in effect means they are
not random anymore. However on way:-

Suppose you want 100 random numbers, fill column A1 to (say)A1000 with the
numbers 1 to 1000 and in B1 enter =Rand() and copy down. Sort the 2 columns
on Column B and select the top 100 numbers in col A as your 100 different
random numbers and copy them into your matrix

Mike
 
L

LtC Baev

Thanks, but it doesn’t work for me. To be more clear, I’ll give you an example:
I’m using =ROUND(RAND()*(999-0)+0;0) to generate three digits random numbers
from 0 to 999. I copyed this formula in a cells A1:E20 (100 cells). In some
generations I’m receiving duplicated numbers in two or more cells, which I
don’t want. This is the problem, wtich I want to solve.
Regards
 
M

Mike

It works for me. Populate a column (Say H) with the numbers 1 to 999 (your
range) and in an adjacent column (I) type =rand() and drag down.

In A1 of your matrix type =H1 and drag down to A20
In B1 of your matrix type =H21 and drag down to B20
Repeat this for all the columns in your array to produce your 5 * 20 matrix.
Hit F9 to make the sheet recalcutate.
Select columns H & I and sort by column I
You now have a matrix of non-repeating random numbers in the tange 1 - 999.

Mike
 
J

Joel

Thsi solution will work. It requires a custtom function

Put in Cell E8 and copy down the row
=SingleRnd(E$7:E7)
Make sure E7 is an empty cell
the random function checks cells in range to make sure no duplicates are
generated

Here is the function
Function SingleRnd(Numbers As Range)


Do While (1)

SingleRnd = Rnd
Found = False

For Each MyNumber In Numbers

If (MyNumber = SingleRnd) Then
Found = True
Exit For
End If

Next MyNumber

If Found = False Then Exit Do
Loop


End Function
 
Top