## [Solved] 4 unique, but random numbers

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

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
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

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).

MrProgrammer
Moderator

Posts: 3964
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: 4 unique, but random numbers

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.

OpenOffice 4.1.3, Windows 10 Home
baro

Posts: 3
Joined: Thu Dec 05, 2019 1:11 pm

### Re: 4 unique, but random numbers

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`
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.5;AOO4.1.7
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.

Zizi64
Volunteer

Posts: 9544
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: 4 unique, but random numbers

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`
OpenOffice 4.1.3, Windows 10 Home
baro

Posts: 3
Joined: Thu Dec 05, 2019 1:11 pm

### Re: 4 unique, but random numbers

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).

MrProgrammer
Moderator

Posts: 3964
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA