Random Number

[Sanjayaranj]

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 .

 

[kweaver]

Welcome to Mr. Excel.

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

 

[Sanjayaranj]

Welcome to Mr. Excel.

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

You are right. That not correct

 

[Sanjayaranj]

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.
	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	3	7	10	18	19		Correct
	1	3	4	21	24	25		Incorrect
	3	4	7	15	18	19		Incorrect
	7	9	14	19	24	25		Correct
	Any formulla? Or VBA code for this type .

 

[Marcelo Branco]

Maybe something like this

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

Pasta1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Numbers
2	1	3	4	7	9	10	11	14	15	18	19	21	24	25
3
4	Exclude			Result
5	1	4		3	9	14	18	21	24
6	1	11
7	1	14
8	4	7
9	4	14
10	4	25
11	15	18
12	15	19
13	21	25
Plan1

Macro

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]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Numbers
2	1	3	4	7	9	10	11	14	15	18	19	21	24	25
3
4	Exclude			Result
5	1	4		3	9	14	18	21	24
6	1	11
7	1	14
8	4	7
9	4	14
10	4	25
11	15	18
12	15	19
13	21	25
Plan1

Macro

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]

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]

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]

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.