# Random Number

#### Sanjayaranj

##### New Member
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
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
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

##### New Member
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

##### MrExcel MVP

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
End With
End Sub``````

Hope this helps

M.

#### Marc L

##### Well-known Member
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

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
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
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
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
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.

Replies
1
Views
46
Replies
7
Views
237
Replies
1
Views
49
Replies
1
Views
55
Replies
3
Views
63

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.

### Which adblocker are you using?

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

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