Sequence Random Number Generator

Reece121

New Member
Joined
Mar 23, 2014
Messages
2
Hello

I would like to create a Random Sequence Number Generator, with No Repeats, but also to variably discount different numbers on a
regular basis.

EG: Numbers are 1 to 100.
I want to create 6 rows of 6 different numbers.
36 in Total, No Repeat.
Discount numbers 10, 25, 30, 85, 99.

Anybody have any suggestions.

Thanks for your time
Reece
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Re: Squence Random Number Generator

Try:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Sub</SPAN> ReturnRands()<br><SPAN style="color:#00007F">Dim</SPAN> DIC                       <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN> <SPAN style="color:#007F00">' Scripting.Dictionary</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> arrOutput(1 <SPAN style="color:#00007F">To</SPAN> 6, 1 <SPAN style="color:#00007F">To</SPAN> 6) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> element                   <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> arrTmp                    <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> n                         <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> x                         <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> y                         <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> lRandNum                  <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <br>  <SPAN style="color:#00007F">Set</SPAN> DIC = CreateObject("Scripting.Dictionary")<br>  <br>  Randomize<br>  <br>  <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> DIC.Count < 36<br>    <br>    lRandNum = Int((100 - 1 + 1) * Rnd + 1)<br>    <br>    <SPAN style="color:#007F00">'// If it's NOT an error, the rand val was found in our list to discount, so skip.  //</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> IsError(Application.Match(lRandNum, Array(10, 25, 30, 85, 99), 0)) <SPAN style="color:#00007F">Then</SPAN><br>      DIC.Item(lRandNum) = <SPAN style="color:#00007F">Empty</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>  <SPAN style="color:#00007F">Loop</SPAN><br>  <br>  arrTmp = DIC.Keys<br>  <br>  <SPAN style="color:#00007F">For</SPAN> y = 1 <SPAN style="color:#00007F">To</SPAN> 6<br>    <SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> 6<br>      arrOutput(y, x) = <SPAN style="color:#00007F">CLng</SPAN>(arrTmp(n))<br>      n = n + 1<br>    <SPAN style="color:#00007F">Next</SPAN> x<br>  <SPAN style="color:#00007F">Next</SPAN> y<br>  <br>  Range("A2").Resize(6, 6).Value = arrOutput<br>  <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Mark
 
Upvote 0
Re: Squence Random Number Generator

I would like to create a Random Sequence Number Generator, with No Repeats, but also to variably discount different numbers on a regular basis.
EG: Numbers are 1 to 100.
I want to create 6 rows of 6 different numbers.
36 in Total, No Repeat.
Discount numbers 10, 25, 30, 85, 99.

I do not understand the part about the discount. If you could give a concrete example, perhaps I can incorporate it. Are those percentages? How are 5 discount numbers applied to 36 random integers? What do you mean by "to variably discount"; randomly? And what do you mean by "on a regular basis"?

To generate a 6-by-6 range of unique random integers, select a 6-by-6 range (e.g. A1:F6) and array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:

=uniqRandBetween(1,100)

Enter the following VBA code:
Code:
Function uniqRandBetween(ByVal lo As Double, ByVal hi As Double)
    ' USAGE:  select 2-dimensional range and
    ' array-enter (press ctrl+shift+Enter instead of Enter)
    ' formula with uniqRandBetween(lowInt,highInt)
    
    Dim nr As Long, nc As Long, n As Long
    Dim i As Long, j As Long, x As Long
    
    Randomize
    
    ' round lo up and hi down if not integers
    lo = WorksheetFunction.RoundUp(lo, 0)
    hi = Int(hi)
    n = hi - lo + 1
    
    ' determine array formula range dimensions
    ' (and number of random numbers)
    With Application.Caller
        nr = .Rows.Count
        nc = .Columns.Count
    End With
    If n < nr * nc Then
        uniqRandBetween = CVErr(xlErrValue)
        Exit Function
    End If
    
    ' generate nr-by-nc array of unique random numbers
    ReDim res(1 To nr, 1 To nc) As Double
    ReDim v(1 To n) As Double
    For i = lo To hi: v(i) = i: Next
    For i = 1 To nr: For j = 1 To nc
        x = Int(n * Rnd) + 1
        res(i, j) = v(x)
        If x <> n Then v(x) = v(n)
        n = n - 1
    Next j, i
    uniqRandBetween = res
End Function

To enter the VBA code:
  1. In Excel, press alt+F11 to open the VBA window.
  2. Copy the code text above, and paste into the VBA editor pane, usually on the left.
  3. You can now close the VBA window, if you wish.
 
Upvote 0
Re: Squence Random Number Generator

I do not understand the part about the discount. If you could give a concrete example, perhaps I can incorporate it. Are those percentages? How are 5 discount numbers applied to 36 random integers? What do you mean by "to variably discount"; randomly? And what do you mean by "on a regular basis"?

To generate a 6-by-6 range of unique random integers, select a 6-by-6 range (e.g. A1:F6) and array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:

=uniqRandBetween(1,100)

Enter the following VBA code:
Code:
Function uniqRandBetween(ByVal lo As Double, ByVal hi As Double)
    ' USAGE:  select 2-dimensional range and
    ' array-enter (press ctrl+shift+Enter instead of Enter)
    ' formula with uniqRandBetween(lowInt,highInt)
    
    Dim nr As Long, nc As Long, n As Long
    Dim i As Long, j As Long, x As Long
    
    Randomize
    
    ' round lo up and hi down if not integers
    lo = WorksheetFunction.RoundUp(lo, 0)
    hi = Int(hi)
    n = hi - lo + 1
    
    ' determine array formula range dimensions
    ' (and number of random numbers)
    With Application.Caller
        nr = .Rows.Count
        nc = .Columns.Count
    End With
    If n < nr * nc Then
        uniqRandBetween = CVErr(xlErrValue)
        Exit Function
    End If
    
    ' generate nr-by-nc array of unique random numbers
    ReDim res(1 To nr, 1 To nc) As Double
    ReDim v(1 To n) As Double
    For i = lo To hi: v(i) = i: Next
    For i = 1 To nr: For j = 1 To nc
        x = Int(n * Rnd) + 1
        res(i, j) = v(x)
        If x <> n Then v(x) = v(n)
        n = n - 1
    Next j, i
    uniqRandBetween = res
End Function

To enter the VBA code:
  1. In Excel, press alt+F11 to open the VBA window.
  2. Copy the code text above, and paste into the VBA editor pane, usually on the left.
  3. You can now close the VBA window, if you wish.


Thanks for the response guys.

I guess I wasn't very clear in my original post.

Numbers are 1 to 100.
I want to create 6 rows of 6 different numbers.
36 in Total, No Repeat.
I do not want to include numbers 10, 25, 30, 85, 99 in this Random Sequence.

These Numbers are not percentages.

What I mean to "variable discount", is that I want to be in the position to be able to change, which numbers I do not want to be included in the Sequence.

Today its 10, 25, 30, 85, 99 which I do not want in the Sequence, tomorrow it might be something else.

I hope I was a bit more clearer.


Thanks Again
Reece
 
Upvote 0
Re: Squence Random Number Generator

Other than the lack of allowing easy substitution of 'discounted' (not allowed) numbers, did you try what I posted at #2?
 
Upvote 0
Re: Squence Random Number Generator

Numbers are 1 to 100.
I want to create 6 rows of 6 different numbers.
36 in Total, No Repeat.
I do not want to include numbers 10, 25, 30, 85, 99 in this Random Sequence.

What I mean to "variable discount", is that I want to be in the position to be able to change, which numbers I do not want to be included in the Sequence.

Today its 10, 25, 30, 85, 99 which I do not want in the Sequence, tomorrow it might be something else.

Give this macro a try (change my example sheet name, highlighted in red, to the actual sheet name you want the 6x6 grid of numbers outputted to)...
Rich (BB code):
Sub SixBySixRandomOneToHundredWithDiscounts()

  Dim X As Long, Z As Long, Count As Long, CountOfDiscounts As Long
  Dim Index As Long, Temp As Long, Numbers() As String
  Static AlreadyRun As Boolean
  
  Const Discounts As String = "10,25,30,85,99"

' Make sure Randomize statement is run only once per session
  If Not AlreadyRun Then
    Randomize
    AlreadyRun = True
  End If
  
 ' Load up an array with all the values from 1 to 100 except for the Discounts
  ReDim Numbers(1 To 99 - UBound(Split(Discounts, ",")))
  Count = 1
  For X = 1 To 100
    If Not "," & Discounts & "," Like "*," & X & ",*" Then
      Numbers(Count) = X
      Count = Count + 1
    End If
  Next
  
  ' Randomly mix up the values in the array
  For X = UBound(Numbers) To LBound(Numbers) Step -1
    Index = Int((X - LBound(Numbers) + 1) * Rnd + LBound(Numbers))
    Temp = Numbers(Index)
    Numbers(Index) = Numbers(X)
    Numbers(X) = Temp
  Next
  
  ' Place the first 36 random values into the worksheet
  For X = 1 To 6
    For Z = 1 To 6
      Sheets("Sheet6").Cells(X, Z) = Numbers(X + 6 * (Z - 1))
    Next
  Next
  
End Sub
Assign your discount numbers to the Discounts constant as a comma delimited list as shown in the blue highlighted line of code.
 
Upvote 0
Re: Squence Random Number Generator

I do not want to include numbers 10, 25, 30, 85, 99 in this Random Sequence. [....] I hope I was a bit more clearer.

Yes. Sorry for my denseness. Try the following code. Usage examples, array-entered in B1:F6 for example:

=uniqrandbetween(1,100,{10,25,30,85,99})
=uniqrandbetween(A1,A2,A3:E3)

where A1=1, A2=100, and A3:E3 contains 10, 25, 30, 85, 99.

Notes:
  1. If the 1st parameter (low) is a non-integer, it is rounded up.
  2. If the 2nd parameter (high) is a non-integer, it is rounded down.
  3. Any non-integer in the 3rd parameter (exclusion list) is rounded.
  4. The function returns #VALUE error if there are inconsistencies in parameters, e.g. low to high less exclusions are fewer than array-formula range.
Rich (BB code):
Function uniqRandBetween(ByVal lo As Double, ByVal hi As Double, _
    Optional exclude)
    ' USAGE:  select 2-dimensional range and
    ' array-enter (press ctrl+shift+Enter instead of Enter)
    ' formula with uniqRandBetween(lowInt,highInt,exclude),
    ' where "exclude" is number, array constant or range of
    ' numbers to be excluded
    
    Dim nr As Long, nc As Long, n As Long
    Dim i As Long, j As Long, x As Long
    Dim nExc As Long, ve As Variant, wf As Variant
    
    Dim s As String
    s = Application.Caller.Address
    
    Randomize
    Set wf = WorksheetFunction
    
    ' round lo up and hi down if not integers
    lo = WorksheetFunction.RoundUp(lo, 0)
    hi = Int(hi)
    n = hi - lo + 1
    
    ' collect exclusion list, if any
    If Not IsMissing(exclude) Then
        Select Case TypeName(exclude)
            Case "Double":
                ReDim exc(1 To 1) As Double
                exc(1) = wf.Round(exclude, 0)
                nExc = 1
            Case "Variant()":
                On Error Resume Next
                nr = UBound(exclude, 1)
                nc = UBound(exclude, 2)
                If Err > 0 Then
                    ' exclude is 1-dim array
                    ReDim exc(1 To nr) As Double
                    For i = 1 To nr
                        exc(i) = wf.Round(exclude(i), 0)
                    Next
                    nExc = nr
                Else
                    ' exclude is 2-dim array
                    ReDim exc(1 To nr * nc) As Double
                    nExc = 0
                    For i = 1 To nr: For j = 1 To nc
                        nExc = nExc + 1
                        exc(nExc) = wf.Round(exclude(i, j), 0)
                    Next j, i
                End If
                On Error GoTo 0
            Case "Range":
                nExc = exclude.Count
                ReDim exc(1 To nExc) As Double
                If nExc = 1 Then
                    exc(1) = wf.Round(exclude, 0)
                Else
                    ve = exclude
                    nr = UBound(ve, 1)
                    nc = UBound(ve, 2)
                    nExc = 0
                    For i = 1 To nr: For j = 1 To nc:
                        nExc = nExc + 1
                        exc(nExc) = wf.Round(ve(i, j), 0)
                    Next j, i
                End If
            Case Else:
                uniqRandBetween = CVErr(xlErrValue)
                Exit Function
        End Select
    End If
    
    ' determine array-formula range dimensions
    ' (and number of random numbers)
    With Application.Caller
        nr = .Rows.Count
        nc = .Columns.Count
    End With
    
    ' generate list of numbers
    ReDim v(1 To n) As Double
    If nExc = 0 Then
        For i = lo To hi: v(i) = i: Next
    Else
        n = 0
        For i = lo To hi
            If IsError(Application.Match(i, exc, 0)) Then
                n = n + 1
                v(n) = i
            End If
        Next
    End If
    If n < nr * nc Then
        uniqRandBetween = CVErr(xlErrValue)
        Exit Function
    End If
    
    ' generate nr-by-nc array of unique random numbers
    ReDim res(1 To nr, 1 To nc) As Double
    For i = 1 To nr: For j = 1 To nc
        x = Int(n * Rnd) + 1
        res(i, j) = v(x)
        If x <> n Then v(x) = v(n)
        n = n - 1
    Next j, i
    uniqRandBetween = res
End Function
 
Last edited:
Upvote 0
Re: Squence Random Number Generator

just for variety and minimum prolixity
Code:
Sub cmvvb()

Const n& = 100
Dim a(n) As Boolean, b, c As Long, d As Long

b = Array(10, 25, 30, 85, 99)    'list your excluded numbers here

For c = 0 To UBound(b)
    a(b(c)) = 1
Next c

Randomize
Do
    c = Int(Rnd * n) + 1
    If Not a(c) Then
        Cells(Int(d / 6) + 1, 1 + d Mod 6) = c
        a(c) = 1
        d = d + 1
    End If
Loop Until d = 36

End Sub
 
Upvote 0
Re: Squence Random Number Generator

Code:
Sub cmvvb()

Const n& = 100
Dim a(n) As Boolean, b, c As Long, d As Long

b = Array(10, 25, 30, 85, 99)    'list your excluded numbers here

For c = 0 To UBound(b)
    a(b(c)) = 1
Next c

Randomize
Do
    c = Int(Rnd * n) + 1
    If Not a(c) Then
        Cells(Int(d / 6) + 1, 1 + d Mod 6) = c
        a(c) = 1
        d = d + 1
    End If
Loop Until d = 36

End Sub

(y) That's sharp Mirabeau; very nice!
 
Upvote 0
Re: Squence Random Number Generator

Errata, for posterity.... Replace
Code:
    If nExc = 0 Then
        For i = lo To hi: v(i) = i: Next
    Else
with
Code:
    If nExc = 0 Then
        For i = 1 To n
            v(i) = i + lo - 1
        Next
    Else
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top