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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Welcome to Mr. Excel.

How can your first "correct" example be correct if the original data set doesn't have a 5?
 
Upvote 0
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 .
 
Upvote 0
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.
 
Upvote 0
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 ?​
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 !​
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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