[Solved] 4 unique, but random numbers

Discuss the spreadsheet application

[Solved] 4 unique, but random numbers

Postby baro » 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!
Attachments
4timesrandom1-39.ods
(18.65 KiB) Downloaded 9 times
Last edited by MrProgrammer on Fri Dec 06, 2019 9:40 pm, edited 2 times in total.
Reason: Split from original topic as details of request are sufficiently different, imo.; Tagged ✓ [Solved] -- MrProgrammer 2019-12-06 07:40 UTC
OpenOffice 4.1.3, Windows 10 Home
baro
 
Posts: 3
Joined: Thu Dec 05, 2019 1:11 pm

Re: 4 unique, but random numbers

Postby MrProgrammer » 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
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3956
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: 4 unique, but random numbers

Postby baro » 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. :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.
OpenOffice 4.1.3, Windows 10 Home
baro
 
Posts: 3
Joined: Thu Dec 05, 2019 1:11 pm

Re: 4 unique, but random numbers

Postby Zizi64 » 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
(20.23 KiB) Downloaded 10 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
Last edited by Zizi64 on Fri Dec 06, 2019 7:01 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.0; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8751
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: 4 unique, but random numbers

Postby baro » Fri Dec 06, 2019 12:48 pm

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
OpenOffice 4.1.3, Windows 10 Home
baro
 
Posts: 3
Joined: Thu Dec 05, 2019 1:11 pm

Re: 4 unique, but random numbers

Postby MrProgrammer » 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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3956
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: robleyd and 10 guests