Page 1 of 1

### [Solved] 4 unique, but random numbers

Posted: Thu Dec 05, 2019 1:21 pm
Hello!

I've been searching a soloution to my problem a very long time. And I still didn't get it.
For a new year lottery (on a small party) I've been trying to make lottery tickets with 4 unique numbers in range from 1 to 39.
There are 500 lottery tickets and in like 10 % of tickets the numbers in 4 columns are not unique. I know there has to be a solution.
So when a random number in column A is generated it mustn't be the same as in columns B,C and D, and when a random
number in column B is selected it mustn't be the same as in columns A,C and D etc.

Can someone modyfy formula in attached file to prevent duplicates?

Sincerelly thank you for your help!

### Re: 4 unique, but random numbers

Posted: Fri Dec 06, 2019 4:14 am
Hi, and welcome to the forum.

baro wrote:4 unique numbers in range from 1 to 39.
I presume you want integers for your numbers.
[Tutorial] Randomization in Calc, technique C. Creating uniformly-distributed unique random integers between specified minimum and maximum values

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

### Re: 4 unique, but random numbers

Posted: Fri Dec 06, 2019 10:12 am
MrProgrammer wrote:Hi, and welcome to the forum.

baro wrote:4 unique numbers in range from 1 to 39.
I presume you want integers for your numbers.
[Tutorial] Randomization in Calc, technique C. Creating uniformly-distributed unique random integers between specified minimum and maximum values

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

Hello! I've tried this before, but it doesn't work for me. I want 4 random unique numbers in 4 columns for 500 times. Technique C stil selects duplicates.
You can see my attacment to get clearer picture.

### Re: 4 unique, but random numbers

Posted: Fri Dec 06, 2019 12:39 pm
If you want four unique random numbers in same time, then you need handle this process with one user defined macro function:

Here is a sample file and rthe code of the macro function:

RND4UniqueInteger.ods

Code: Select all   Expand viewCollapse view
`REM  *****  BASIC  *****Option explicitFunction FourUniqIntRnd(LowerLimit as integer, UpperLimit as integer) as Variant REM This user defined function works as an 'array function'. The results will be returned into a cell range. dim Output_Array(3) REM The output values will be returned into four adjacent cells in same row. REM The "3" is gives a horizontal dimension of the output array. REM But you can modify the code with the usage of a two dimensional array: REM dim Output_Array(0,3) then the results will be returned in one column (the "3" is the vertical dimension in this case). REM You need modify all of lines of the code REM that contains the variable Output_Array(x)  to Output_Array(0,x) dim i as integer dim OneRndNumber as integer 'Randomize 2^14-1   If LowerLimit >= UpperLimit then      Output_Array(0) = -1      Output_Array(1) = -1      Output_Array(2) = -1      Output_Array(3) = -1      FourUniqIntRnd = Output_Array      Exit function   end if      For i= 0 to 3      Output_Array(i) = 0   next i      For i = 0 to 3       OneRndNumber = Int(0.5 + rnd*(UpperLimit - LowerLimit)) + LowerLimit      Do While ((OneRndNumber = Output_Array(0)) or (OneRndNumber = Output_Array(1)) or (OneRndNumber = Output_Array(2)) or (OneRndNumber = Output_Array(3)))         OneRndNumber = Int(0.5 + rnd*(UpperLimit - LowerLimit)) + LowerLimit        Loop         Output_Array(i) = OneRndNumber   next i   FourUniqIntRnd = Output_ArrayEnd function`

### Re: 4 unique, but random numbers

Posted: Fri Dec 06, 2019 12:48 pm
Zizi64!

That's what I needed!!! Thank you very, very much!!!!!!!

Zizi64 wrote:If you want four unique random numbers in same time, the you need handle this process with one user defined macro function:

Here is a sample file and rthe code of the macro function:

RND4UniqueInteger.ods

Code: Select all   Expand viewCollapse view
`REM  *****  BASIC  *****Option explicitFunction FourUniqIntRnd(LowerLimit as integer, UpperLimit as integer) as Variant REM This user defined function works as an 'array function'. The results will be returned into a cell range. dim Output_Array(3) REM The output values will be returned into four adjacent cells in same row. REM The "3" is gives a horizontal dimension of the output array. REM But you can modify the code with the usage of a two dimensional array: REM dim Output_Array(0,3) then the results will be returned in one column (the "3" is the vertical dimension in this case). REM You need modify all of lines of the code REM that contains the variable Output_Array(x)  to Output_Array(0,x) dim i as integer dim OneRndNumber as integer 'Randomize 2^14-1   If LowerLimit >= UpperLimit then      Output_Array(0) = -1      Output_Array(1) = -1      Output_Array(2) = -1      Output_Array(3) = -1      FourUniqIntRnd = Output_Array      Exit function   end if      For i= 0 to 3      Output_Array(i) = 0   next i      For i = 0 to 3       OneRndNumber = Int(0.5 + rnd*(UpperLimit - LowerLimit)) + LowerLimit      Do While ((OneRndNumber = Output_Array(0)) or (OneRndNumber = Output_Array(1)) or (OneRndNumber = Output_Array(2)) or (OneRndNumber = Output_Array(3)))         OneRndNumber = Int(0.5 + rnd*(UpperLimit - LowerLimit)) + LowerLimit        Loop         Output_Array(i) = OneRndNumber   next i   FourUniqIntRnd = Output_ArrayEnd function`

### Re: 4 unique, but random numbers

Posted: Fri Dec 06, 2019 9:38 pm
baro wrote:Technique C [still] selects duplicates. You can see my [attachment] to get clearer picture.
You only have one attachment, 4timesrandom1-39.ods in your first post. I cannot find any evidence in that spreadsheet that you have implemented technique C. In which cells did you do that? Using =RANDBETWEEN(1; 39) is not technique C. Based on the "That's what I needed!!", I will mark this as solved.

Zizi64 wrote:If you want four unique random numbers in same time, then you need handle this process with one user defined macro function:
In my attachment to the randomization tutorial, you will find sheet RandUnique. If you put 4, 1, and 39 in B2:B4 you'll get four distinct random integers in B5:B8. The attachment does not use any macros. I suspect that your macro will be simpler for the OP, however.