PDA

View Full Version : Unique Random Number Generation



MINCUS1308
07-18-2018, 04:47 AM
I'm making minesweeper in excel because they don't have any games on the computers at work.
As part of this, I'm attempting to generate 'x' unique random numbers and then store them in MyArray.



For i = 1 To NoOfMines
Do
MineValue = CInt(Int((Size * Size - 1) * Rnd + 1))
Loop Until IsError(Application.Match(CInt(MineValue), MyArray, 0))
MyArray(i, 0) = MineValue
Next I


Typically I generate 'x-1' unique numbers with 1 number repeated. '[(x-1)+1=x]
Every now and again I get 'x' unique numbers but its pretty rare.

I cannot identify a pattern of repeats associated with the iteration of 'i'.
Any thoughts about the folly in my logic?

Jan Karel Pieterse
07-18-2018, 07:02 AM
I would simply create a two-column table with all the mines in columnA and the rand function in B and then do a calc and subsequently sort on col B. Then simply take the number of items from the top.

Furtado
07-18-2018, 07:26 AM
Hi Jan, can you explain how to do this exactly, please? For the less knowledgeable people like me. It looks like this is perfect for something I'm doing as well. Cheers.

Jan Karel Pieterse
07-18-2018, 07:34 AM
Suppose you have employee names in column A (starting from A2) and you want to randomly draw 10 employees.
In B2, enter =RAND()
copy down to match the # of employees
Select A and B and sort on column B. the top 10 employees are your draw. Want a new set of 10? simply sort on column B again.

Paul_Hossler
07-18-2018, 07:52 AM
This might be a little simpler




Option Explicit

Sub MineSweeper()

Const Size As Long = 10
Const NumMines As Long = 20

Dim MineField(1 To Size, 1 To Size) As Boolean
Dim r As Long, c As Long, n As Long

n = 0

Randomize
Do While n < NumMines

r = Int(Size * Rnd + 1)
c = Int(Size * Rnd + 1)

If Not MineField(r, c) Then
MineField(r, c) = True
n = n + 1
End If
Loop
End Sub

snb
07-18-2018, 08:50 AM
Store the result in an Array 'sn':

Sub M_snb()
[A1:A20] = "=rand()"
sn = [index(rank(A1:A20,A1:A20),)]
[A1:A20].clearcontents
End Sub

Store the results in the worksheet

Sub M_snb()
[A1:A20] = "=rand()"
[A1:A20] = [index(rank(A1:A20,A1:A20),)]
End Sub