Static Random numbers

English
#1

I’m creating a pool draw/bracket for our pool night.
I have a name entry sheet where the number of players is entered and then the names are chosen from a dropdown list. When the right number of players is selected I need a random number beside each name with no duplicates, and if 12 players (in D2) then 1-12 only.
=IF(L10=“yES”,SORTBY(SEQUENCE(D2),RANDARRAY(D2)),"")
These numbers are then used on other sheets (17 sheets for 8 to 24 players) to populate the brackets with players names from a vlookup function in a set order for each draw with byes etc
The program is basically done but for the random number function being volitile and rendering the bracket sheets useless with the names changing all the time.
Is there a way to have the numbers remain static (a RANDARRAY.NV would be good).
Any help to quench my headache with this conundrum appreciated.

#2

RANDARRAY.NV() is requested with tdf#163655.

#3

So, right now you are just pasting in generated values as numbers only and using that range, right? So you have what you want, you just want something that doesn’t require messy intervention? You just need one seed per run of the program? A macro that would grab your random list using named ranges and getDataArray() then drop those fixed values into the actual program-used range with setDataArray would seem pretty straightforward. Then have the project run off that copy (which will be static).

Here’s what Gemini created for me…it tests okay and makes sense:

Sub TestCNRV()
	CopyNamedRangeValues("Source","Target")
End Sub
Sub CopyNamedRangeValues(sSourceRangeName As String, sTargetRangeName As String)

    Dim oDoc As Object
    Dim oSheet As Object ' This will be the sheet of the target range
    Dim oSourceRange As Object
    Dim oTargetRangeBase As Object ' The initial target range object
    Dim oTargetAdjustedRange As Object ' The adjusted target range for setDataArray
    Dim aData As Variant
    Dim nSourceRows As Long
    Dim nSourceCols As Long
    Dim nTargetRowStart As Long
    Dim nTargetColStart As Long

    oDoc = ThisComponent

    On Error GoTo ErrorHandler

    ' Get the source named range
    oSourceRange = oDoc.NamedRanges.getByName(sSourceRangeName).getReferredCells()

    ' Get the initial target named range
    oTargetRangeBase = oDoc.NamedRanges.getByName(sTargetRangeName).getReferredCells()

    ' Determine source dimensions
    nSourceRows = oSourceRange.Rows.Count
    nSourceCols = oSourceRange.Columns.Count

    ' Get the top-left cell coordinates of the target range
    oSheet = oTargetRangeBase.Spreadsheet
    nTargetRowStart = oTargetRangeBase.RangeAddress.StartRow
    nTargetColStart = oTargetRangeBase.RangeAddress.StartColumn

    ' Adjust the target range to match the source range dimensions,
    ' starting from the top-left of the original target range.
    oTargetAdjustedRange = oSheet.getCellRangeByPosition( _
        nTargetColStart, _
        nTargetRowStart, _
        nTargetColStart + nSourceCols - 1, _
        nTargetRowStart + nSourceRows - 1 _
    )

    ' Get the data array from the source range (values only)
    aData = oSourceRange.getDataArray()

    ' Set the data array to the adjusted target range
    oTargetAdjustedRange.setDataArray(aData)

    MsgBox "Values copied successfully from '" & sSourceRangeName & "' to '" & sTargetRangeName & "'." & Chr(13) & _
           "Target range dynamically adjusted to " & oTargetAdjustedRange.AbsoluteName & ".", _
           vbInformation, "Copy Complete"

    Exit Sub

ErrorHandler:
    Select Case Err
        Case 423 ' Object not found (e.g., named range doesn't exist)
            MsgBox "Error: One of the specified named ranges does not exist." & Chr(13) & _
                   "Please check your range names: " & sSourceRangeName & " and " & sTargetRangeName, _
                   vbCritical, "Range Not Found"
        Case Else
            MsgBox "An unexpected error occurred: " & Err.Description & " (Error " & Err.Number & ")", _
                   vbCritical, "Error"
    End Select

End Sub

Gemini gets a little windy at times, but I suppose that’s better than cryptic.

#4

RANDARRAY() Has four parameters!

  • For a single column of random numbers you don’t need a non-volatile RANDARRAY(). You need random numbers for which you can trust in their being distinct.
    RAND.NV() filled down will suffice due to the algorithm behind.
  • A randomized sequence created once and then copied / pasted In the right place would do better than the automation you have in mind.
    What about a case where you need a complete recalculation to get a mistake straightened out.
  • If automation is indispensable it should be implemented, imo, by a macro in such a case,

= = Editing = =
Sorry. Pots crossed.

1 Like
#5

Fill Rand

#6

Thanks for your efforts.
The main reason for wanting the auto process on the starter sheet is, a few different people run the pool on Friday night so I would like the process to be as easy as possible ie minimal keyboard entry
I’ve attached the 3 relevant sheets (startup,names,GO8) would you be able to add the macro in with someway to trigger the macro to do its thing when the number of names entered = D2.
The start sheet is as it is now with the volitile random numbers starting in A7.
Go8 brackets are populated when the associated random numbers are set. The C column is a dropdown list (W/L) which then populates the next bracket with the winner etc. The green box is the table number (ours is 1-4)
Hopefully this copy works for you.
Your help is very much appreciated
Pool.ods (20.4 KB)

Garry

#13

It’s like shooting at sparrows with cannons, but with the help of a marginal benefit of Lupp’s BSM package, you can do it that way too:
nonVolatileRandarrayWithoutTemplateSheets.ods (34.8 KB)
Possibly there is one or another advanced user (@Villeroy ?, @Sokol92 ? … ) interested in the package. There is a template containing
the code and much more information about the usage, and there are more examples.

#7

Here is a bespoke solution using a BASIC macro. Although this is a pretty specific macro, the mechanics should be usable for other applications.

Sub CreateDerrangement()
	Dim Count As Long
	Dim FillRange As Object
	Dim FillCount
	Dim RandNums
	Dim Index
	Dim Subdex
	Dim Allow
		
	Count = ThisComponent.NamedRanges.getByName("PlayerCount").ReferredCells.DataArray(0)(0)
	FillRange = ThisComponent.NamedRanges.getByName("RandomSet").ReferredCells
	FillCount = FillRange.RangeAddress.EndRow - FillRange.RangeAddress.StartRow + 1
	If Count > FillCount Then Count = FillCount
	ReDim RandNums(0 To FillCount - 1)
	For Index = 0 To Count - 1
		RandNums(Index) = Array(0)
		RandNum = 1+INT(Rnd*Count)
		Allow = True
		For Subdex = 0 To Index - 1
			If RandNums(Subdex)(0) = RandNum Then
				Index = Index - 1
				Allow = False
				Exit For
			Else
			EndIf
		Next Subdex
		If Allow Or Index = 0 Then RandNums(Index)(0) = RandNum
	Next Index
	For Index = Index To FillCount - 1
		RandNums(Index) = Array(0)
	Next Index
	FillRange.setDataArray(RandNums)
	
End Sub

I’ve attached your Pool spreadsheet with the macro in place.

Pool.ods (32.5 KB)

#8

Hi Joshua
Thanks for that awesome and quick fix with my random number problem.
As I can’t seem to be able to alter anything you have done can you please help with the following;

I see you have included 2 ‘set names’ boxes which work a bit too well.
–Could you please delete the box on the starter sheet. [redundant with box on Go sheets and no reason for numbers to be seen here]
–With the box on Go8, can you make it so it either deletes or hides after one click so it can’t be activated many times, just the once.
[The person who does the draw could just bang the button and flick the names to whatever is best for themselves, as they play too. Once the names are set that’s the end of its job so can be done away with. As I see it?. Maybe they wouldn’t fiddle with it as it’s on screen but I’d rather not give them the option.]
–Can the background in box on Go8 be a light green? [to pretty it up].

–On the starter sheet can only the B7+ cells be highlighted based on the entered B4 cell value ie 10 players so B7-B16 only are colored. [Defines the correct number of names are in when all colored cells are filled]. Can the A column cells be blank on this sheet rather than showing ‘Unused’

I’ll eventually be using this sheet as a template as there are 17 other Go sheets (7-24) to cater for all the draw variations (number of players). So I’m hoping that your code will work the same way across all sheets.

I’ve done quite a bit of programming over the years, basic, VB, some COBOL/Pascal, some C++ etc but I can’t decipher that code you (or Gemini) come up with,
Again, your help muchly Appreciated!!.
Garry

#9

Tools>Forms>Design Mode toggles moving, marking, deleting buttons like Set Names. You add them using Insert>Form Control>Push Button.

Also, notice there have been some style applied. View>Sidebar then click on the A button toward the upper right of the sidebar. Then right click the style that is highlighted when the cell whose style you want to change is selected on the spreadsheet. from the drop menu (from right clicking) select Edit style, then Background tab (or Background in left pane perhaps in 25.2) and pick your color.

Styles, styles, styles! They let you design a dashboard then completely rework it with very little effort. There are even quicker ways (A with recycle arrows button in sidebar after editing a selected cell style) to update any style.

[READ then FORGET this paragraph…SEE BELOW: Anticipating your need for different bracket sheets, I also setup modular math for the initial and second-life positions. The 1 has to stay 1 at the top left (1st left position number) but the one right below it (2nd position number) can be copied down to 4th, and from then on the next pair (3rd and 4th position numbers, 2nd set of competitors) can be copied down indefinitely. They listen to the 8 up in B1 (or near there). The right pairs (second-life pairs) can be copied down in pairs indefinitely starting with the top pair (1st and 2nd position number to the right).]

EDIT: Further tested the bracket position numbers and redid them so they are based on a simple nth value column. See new version attached. You can just hide the numeric columns or format them so that the font is the same as the background (hence, invisible)–using styles! You can just pull down the (here darker grey) column right through, so you don’t have to maintain row spacing, you just have to have the 1, 2, 3, … sequence where needed in column A any how that works for a given bracket.

I also implemented @JohnSUN’s Peter-do-you-love-me approach to slowing down button clicks.

Pool.ods (33.9 KB)

#10

I understand what you are writing about. Yes, I have also encountered users who, due to absentmindedness, could press the button at the wrong time. Since blocking the button is not a trivial task, I decided to simply add three MsgBoxes with “yes/no” buttons and questions requiring different correct answers to the beginning of the macro:
Are you sure you want to generate a new tournament table?
Existing data will be destroyed - return to the current state?
Leave the table as is?
And only after receiving the “Yes-No-No” answers, I performed the rest of the actions.
The idea was that an absent-minded user would never read the texts in MsgBox and would simply always click Yes. It worked - no one corrupted the data.

#11

Hi Joshua
With the ‘Set Names’ button could your code go into the ‘Key Pressed’ event and the disable it go in the ‘Key released’ event.
Would that give me a one press only and no more
Silly or possible?
Garry

addendum

#12

As @JohnSun said, getting into the weeds of the UNO event model is seldom worth it. I’ve updated the file to just use a ‘secret’ cell to flag whether or not the button (really the macro itself) is operational. The macro checks for the value 1 in this cell and exits if it is not there. To set or reset the system so the button works (macro works), assign the ResetOneShot macro to a ‘secret’ keystroke. Tools>Customize…>Keyboard tab>Category pane>Application macros then select the ResetOneShot macro. Find your keystroke above, click on it, then press the Assign button. If you select your keystroke option first then the macro that’s fine, but you still have to press Assign. Now when you press that keystroke, you’ll get a popup saying the button has been reset, and it has been.
This system even persists across file loads. So, unless you have a power user working on it, only a person with the ‘secret’ keystroke can reset the button.
Bracket for Pool Gazzal.ods (35.5 KB)

#14

Hi Joshua
Many thanks for that program, works beautifully!.
I Love the MMath for the singles games!.
But I’ve sent you a workbook that includes a PAIRS match, Go8-P, which works like so.
Obviously there are 16 players.
Numbers are set and each pair goes in order ie;
1 & 2 (Reece & Kim), 3 & 4 (Sue & Brad) etc and then each pair is entered in the bracket as per singles. It’s just getting those numbers to work with yr system.
All I’ve done is stretched out the columns a bit to fit the two names in.
Can you please, again, show me how to do this on the example sheet.

There is a Go9-S sheet there proving the math works - Cool🌟
Garry

Bracket for Pool Gazzal.ods (42.8 KB)

#15

I may have spent too much time on this :upside_down_face:. But I think the project may be worthy of going onto the LibreOffice Extensions website as a template document, once completed.
Check out the automation. The 8 Pair sheet has lots of comments.
I reworked formatting using a mixture of styles and conditional formatting. Very little single-cell formatting should be needed. Really, just setting the cells that have visible content to be styled as Entry will go a very long way.
Many columns can now just be pulled down for however tall the bracket is. Other columns can be mimicked pretty easily. Please do read the comments on the example sheet.
Please post back how things are going, and I’m happy to lend a hand for final touches before posting on the Extensions site.
If you are not a seasoned user of Excel or Calc, understanding this automation will not be easy!! But using it to ‘paint’ the brackets actively should be pretty straightforward.
Brackets for Pool Auto.ods (46.2 KB)
Oh, to be honest, I’m not sure if the Winner… functions need to be entered as array formulas or not. It seems to work either way, and is a real testament to the internals of Calc. I think entering as an array formula (even though just one cell) is the ‘safer’ option.
Also, merged cells are ‘evil’. Avoid them whenever possible…

#16

HI
Thanks for that tremendous effort done so quickly too. Nice looking form!!.

On pressing the Set Names box [Shift/F7 to run] an ‘exception occurred’ which hasn’t happened b4. Type: com.sun.star.container.NoSuchElementException
In this line; Count = ThisComponent.NamedRanges.getByName(“PlayerCount”).ReferredCells.DataArray(0)(0)

I have been trying to decipher your notes so have been playing around a little bit. So not sure if it’s your new stuff or me.

I’ve noticed you’ve taken over my ‘Table Number’ green boxes with yr TD arrows “C” so I’m gonna have to take the green boxes back and put yr TD arrow in next column about .17cm wide?. Funny; I did something on the sheet and pop, all the TD arrows went haywire and Ctrl Z didn’t help, looked a right sight. So a bit of work to get them back on track.
I’ve included the sheets again with a few adjusted TD’s but haven’t worked out how to get the green background yet for the ‘Table’ box. Needs a table style?.
Still not sure if on starting this program that the ‘Enter Players’ field on starter sheet resets to zero and names are cleared or not, do they?.

Still reading at kindergarten level😂

Garry

Brackets for Pool Auto.ods (46.2 KB)

#17

Repaired the PlayerCount problem. It is part of preparing to put the random list per sheet…I don’t think having a single random list is going to be convenient in the end. That requires some data vectoring, so not today.

Added back little pool tables for the pool table numbers and abstracted the named expressions to allow that sort of thing more easily, but it wasn’t difficult, really. Since these cells need to hold the table numbers, they have to be formatted as such to the Location style, but you can copy a whole game unit (connector boxes, table number, player names, win/loss) at once, so that shouldn’t matter much.
Brackets for Pool Auto.ods (42.3 KB)

You might try building a sheet for a four-player bracket, no extra life, or something like that to see if the “painting” with the named expressions works.

Oh, and if you get the 504 errors, try Ctrl+Shift+F9 let me know if they persist…seem to be a quirk in Calc itself. If so, comment on your LO version, too, by copying the Help>About information with the little copy button on the About splash dialog.

#18

Thanks
I have quickly made a TEST 8-S sheet for your critique.
Problems with this version;
The Set Names box has lost it’s magic (msgbx) so I can smack the numbers willy nilly
The B col random numbers are not unique
Garry

Brackets for Pool Auto (2).ods (51.1 KB)

#19

Lots of advancement. Look forward to:

  • No distinction between single-player and pair sheets–one sheet does it all for 8 players or 8 teams
  • Each bracket sheet has its own player list–multiple simultaneous tournaments are possible
  • Player lists can be persisted (stored) – keep the Monday night crowd list and have another for Fridays
  • Player list entry gives count affirmation (like you had) plus flags repeat entries
  • Player raw list moved from names sheet to player list sheet. This is ?? but seemed easier to, say, correct a spelling or add a name quickly
  • Final playoff (center of sheet) is now auto-populated
  • Lots of bug fixes

However, the 504 error is not totally gone yet.
Notice that the one-shot protection is intentionally disabled for testing.
Deadwood random numbers are just left sitting at the left of the player list right now…they are ignored (I think :slight_smile:).

Edit: Corrected scripting error

Brackets for Pool Auto Jo4.ods (51.4 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.