Random Number

Sanjayaranj

New Member
Joined
May 7, 2021
Messages
8
Office Version
  1. 2010
Hi Everybody ,
I need a help .
How to generate random numbers with some conditions.

Conditions
1. using these numbers.
1 3 4 7 9 10 11 14 15 18 19 21 24 25

2.I don’t need this pairs in a row
1 4
1 11
1 14
4 7
4 14
4 25
15 18
15 19
21 25

3. I need six numbers in a row


Examples

1 5 7 10 18 19 Correct
1 3 4 21 24 25 Incorrect
4 5 7 15 18 19 Incorrect
5 9 14 19 24 25 Correct



Any formulla? Or VBA code for this type .
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,062
Office Version
  1. 365
  2. 2010
Welcome to Mr. Excel.

How can your first "correct" example be correct if the original data set doesn't have a 5?
 

Sanjayaranj

New Member
Joined
May 7, 2021
Messages
8
Office Version
  1. 2010
You are right. That not correct
Hi Everybody ,
I need a help .
How to generate random numbers with some conditions.
Conditions
1. using these numbers.
13479101114151819212425
2.I don’t need this pairs in a row
14
111
114
47
414
425
1518
1519
2125
3. I need six numbers in a row
Examples
137101819Correct
134212425Incorrect
347151819Incorrect
7914192425Correct
Any formulla? Or VBA code for this type .
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,653
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Maybe something like this

Numbers in A2:N2
Exclusion list (pairs) in A5:B13
Result in D5:I5

Pasta1
ABCDEFGHIJKLMN
1Numbers
213479101114151819212425
3
4ExcludeResult
5143914182124
6111
7114
847
9414
10425
111518
121519
132125
Plan1


Macro
VBA Code:
Sub SixRandom()
    Dim dic As Object, CurrentKey As Long
    Dim rcell As Range, arrResult(1 To 6) As Long, i As Long
    
    Set dic = CreateObject("Scripting.Dictionary")
    For Each rcell In Range("A2:N2")
        dic(rcell.Value) = Empty
    Next rcell
    
    For i = 1 To 6
        CurrentKey = Application.Index(dic.keys, Application.RandBetween(1, dic.Count))
        arrResult(i) = CurrentKey
        dic.Remove CurrentKey
        For Each rcell In Range("A5:A13")
            If rcell.Value = CurrentKey Then
                If dic.exists(rcell.Offset(, 1).Value) Then dic.Remove rcell.Offset(, 1).Value
            ElseIf rcell.Offset(, 1) = CurrentKey Then
                If dic.exists(rcell.Value) Then dic.Remove rcell.Value
            End If
        Next rcell
    Next i
    
    With Range("D5").Resize(1, 6)
        .Value = arrResult
        .Sort key1:=.Cells(1), Orientation:=xlSortRows, Header:=xlNo
    End With
End Sub

Hope this helps

M.
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,134
Office Version
  1. 2010
Platform
  1. Windows
1. using these numbers.
1 3 4 7 9 10 11 14 15 18 19 21 24 25

2.I don’t need this pairs in a row
1 4
1 11
1 14
4 7
4 14
4 25
15 18
15 19
21 25

3. I need six numbers in a row
Hi,​
all numbers & pairs can be hardcoded - never change - or you forgot to well elaborate your need or an attachment ?​
Six numbers in a row, ok but which one, where should be the result ?​
 

Sanjayaranj

New Member
Joined
May 7, 2021
Messages
8
Office Version
  1. 2010

ADVERTISEMENT

Hi,​
all numbers & pairs can be hardcoded - never change - or you forgot to well elaborate your need or an attachment ?​
Six numbers in a row, ok but which one, where should be the result ?​

Hi,​
all numbers & pairs can be hardcoded - never change - or you forgot to well elaborate your need or an attachment ?​
Six numbers in a row, ok but which one, where should be the result ?​

Maybe something like this

Numbers in A2:N2
Exclusion list (pairs) in A5:B13
Result in D5:I5

Pasta1
ABCDEFGHIJKLMN
1Numbers
213479101114151819212425
3
4ExcludeResult
5143914182124
6111
7114
847
9414
10425
111518
121519
132125
Plan1


Macro
VBA Code:
Sub SixRandom()
    Dim dic As Object, CurrentKey As Long
    Dim rcell As Range, arrResult(1 To 6) As Long, i As Long
   
    Set dic = CreateObject("Scripting.Dictionary")
    For Each rcell In Range("A2:N2")
        dic(rcell.Value) = Empty
    Next rcell
   
    For i = 1 To 6
        CurrentKey = Application.Index(dic.keys, Application.RandBetween(1, dic.Count))
        arrResult(i) = CurrentKey
        dic.Remove CurrentKey
        For Each rcell In Range("A5:A13")
            If rcell.Value = CurrentKey Then
                If dic.exists(rcell.Offset(, 1).Value) Then dic.Remove rcell.Offset(, 1).Value
            ElseIf rcell.Offset(, 1) = CurrentKey Then
                If dic.exists(rcell.Value) Then dic.Remove rcell.Value
            End If
        Next rcell
    Next i
   
    With Range("D5").Resize(1, 6)
        .Value = arrResult
        .Sort key1:=.Cells(1), Orientation:=xlSortRows, Header:=xlNo
    End With
End Sub

Hope this helps

M.
Thank you so much Marcelo😄.it is working. but it is generate only one number serial an each time.How i get get all related number serial in one click.
Thank you.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,653
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Thank you so much Marcelo😄.it is working. but it is generate only one number serial an each time.How i get get all related number serial in one click.
Thank you.
You are welcome.
By the way, i don't understand what you mean by "get all related number serial in one click"

M.
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,134
Office Version
  1. 2010
Platform
  1. Windows
but it is generate only one number serial an each time.How i get get all related number serial in one click.
If only you have well elaborated your initial post in order there is nothing to guess as you never mentioned that !​
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,653
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Sanjayaranj,

If you need to generate all the possibilities, this makes the code much more complex - I don't know how to calculate how many different sets would be possible (???)

I'm afraid I don't know how to do this.

M.
 

Forum statistics

Threads
1,136,277
Messages
5,674,795
Members
419,525
Latest member
mountainkids

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
Top