Page 1 of 1

[Solved] 4 unique, but random numbers

PostPosted: Thu Dec 05, 2019 1:21 pm
by baro
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

PostPosted: Fri Dec 06, 2019 4:14 am
by MrProgrammer
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

PostPosted: Fri Dec 06, 2019 10:12 am
by baro
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. :cry: 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.

Thank you for your help.

Re: 4 unique, but random numbers

PostPosted: Fri Dec 06, 2019 12:39 pm
by Zizi64
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
(20.23 KiB) Downloaded 11 times


Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****
Option explicit

Function 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_Array
End function

Re: 4 unique, but random numbers

PostPosted: Fri Dec 06, 2019 12:48 pm
by baro
Zizi64! :bravo:

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 explicit

Function 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_Array
End function

Re: 4 unique, but random numbers

PostPosted: Fri Dec 06, 2019 9:38 pm
by MrProgrammer
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.