How to generate matrix of nonrepeated random numbers in Exel?

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

"LtC Baev" wrote:

How to generate matrix of nonrepeated random numbers in Exel?

Thanks, but it doesnt work for me. To be more clear, Ill give you an example:
Im 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 Im receiving duplicated numbers in two or more cells, which I
dont want. This is the problem, wtich I want to solve.
Regards

"Mike" wrote:

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

"LtC Baev" wrote:

How to generate matrix of nonrepeated random numbers in Exel?

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

"LtC Baev" wrote:

Thanks, but it doesnt work for me. To be more clear, Ill give you an example:
Im 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 Im receiving duplicated numbers in two or more cells, which I
dont want. This is the problem, wtich I want to solve.
Regards

"Mike" wrote:

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

"LtC Baev" wrote:

How to generate matrix of nonrepeated random numbers in Exel?

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




"LtC Baev" wrote:

Thanks, but it doesnt work for me. To be more clear, Ill give you an example:
Im 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 Im receiving duplicated numbers in two or more cells, which I
dont want. This is the problem, wtich I want to solve.
Regards

"Mike" wrote:

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

"LtC Baev" wrote:

How to generate matrix of nonrepeated random numbers in Exel?

Hello,

Take my function UniqRandInt from www.sulprobil.com, I suggest.

Regards,
Bernd