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

#### Marcelo Branco

##### MrExcel MVP
It is a controversial subject ...
See topic
Performance: Dictionary vs. Collection

M.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Marc L

##### Well-known Member
If one day you have more than 500 000 elements within a Dictionary just keep in mind to try a VBA Collection …​

#### Sanjayaranj

##### New Member
Well, gosh, Marcello, you guilted me into cleaning up my macro!

I added one cell in B3 to show how many numbers to include in the combinations:

Book1
ABCDEFGHIJKLMN
1Numbers
213479101114151819212425
3Size6
4ExcludeResults
514
6111
7114
847
9414
10425
111518
121519
132125
Sheet14

Then here is the macro:

VBA Code:
Option Explicit

Sub GetCombos()
Dim nums As Variant, ex As Object, rslts As Object, i As Long, siz As Long

nums = Range("A2:" & Cells(2, Columns.Count).End(xlToLeft).Address).Value
siz = Range("B3").Value
Set ex = CreateObject("Scripting.Dictionary")
Set rslts = CreateObject("Scripting.Dictionary")
For i = 5 To Cells(Rows.Count, "A").End(xlUp).Row
ex.Add Cells(i, "A") & "/" & Cells(i, "B"), 1
Next i

Call recur(1, "", nums, ex, 0, siz, rslts)

Range("D5").Resize(rslts.Count).Value = WorksheetFunction.Transpose(rslts.keys)
Range("D5").Resize(rslts.Count).TextToColumns Destination:=Range("D5"), Comma:=True
End Sub

Sub recur(ByVal strt As Long, res As String, ByRef nums, ByRef ex, ByVal lvl As Long, siz As Long, ByRef rslts As Object)
Dim i As Long, MyFlag As Boolean, x As Variant

If lvl = siz Then
Exit Sub
End If

For i = strt To UBound(nums, 2)
MyFlag = True
For Each x In Split(res, ",")
If ex.exists(x & "/" & nums(1, i)) Then
MyFlag = False
Exit For
End If
Next x
If MyFlag Then Call recur(i + 1, res & nums(1, i) & ",", nums, ex, lvl + 1, siz, rslts)
Next i

End Sub

The number of numbers in A2:N2 is variable, just enter as many as needed. The number of exclusions is variable, just add as many rows as needed. The numbers in row 2 should be sorted. The exclusions in columns A:B should have the lower number first.

Well, gosh, Marcello, you guilted me into cleaning up my macro!

I added one cell in B3 to show how many numbers to include in the combinations:

Book1
ABCDEFGHIJKLMN
1Numbers
213479101114151819212425
3Size6
4ExcludeResults
514
6111
7114
847
9414
10425
111518
121519
132125
Sheet14

Then here is the macro:

VBA Code:
Option Explicit

Sub GetCombos()
Dim nums As Variant, ex As Object, rslts As Object, i As Long, siz As Long

nums = Range("A2:" & Cells(2, Columns.Count).End(xlToLeft).Address).Value
siz = Range("B3").Value
Set ex = CreateObject("Scripting.Dictionary")
Set rslts = CreateObject("Scripting.Dictionary")
For i = 5 To Cells(Rows.Count, "A").End(xlUp).Row
ex.Add Cells(i, "A") & "/" & Cells(i, "B"), 1
Next i

Call recur(1, "", nums, ex, 0, siz, rslts)

Range("D5").Resize(rslts.Count).Value = WorksheetFunction.Transpose(rslts.keys)
Range("D5").Resize(rslts.Count).TextToColumns Destination:=Range("D5"), Comma:=True
End Sub

Sub recur(ByVal strt As Long, res As String, ByRef nums, ByRef ex, ByVal lvl As Long, siz As Long, ByRef rslts As Object)
Dim i As Long, MyFlag As Boolean, x As Variant

If lvl = siz Then
Exit Sub
End If

For i = strt To UBound(nums, 2)
MyFlag = True
For Each x In Split(res, ",")
If ex.exists(x & "/" & nums(1, i)) Then
MyFlag = False
Exit For
End If
Next x
If MyFlag Then Call recur(i + 1, res & nums(1, i) & ",", nums, ex, lvl + 1, siz, rslts)
Next i

End Sub

The number of numbers in A2:N2 is variable, just enter as many as needed. The number of exclusions is variable, just add as many rows as needed. The numbers in row 2 should be sorted. The exclusions in columns A:B should have the lower number first
Thank you so much Eric.It is very useful to me.

Last edited:

#### Marc L

##### Well-known Member
It is a controversial subject ...
See topic
Performance: Dictionary vs. Collection

M.
Some bad things within this link in particular when the author stated he needs two VBA Collections to retrieve the keys​
but in fact it can be easily achieved with a single collection as I have created a Dictionary like class module​
to convert any Dictionary Windows VBA procedure on Mac or for the Windows users with some OS restrictions according to their companies rules …​

Replies
1
Views
47
Replies
7
Views
239
Replies
1
Views
51
Replies
1
Views
67
Replies
3
Views
65

1,136,449
Messages
5,675,907
Members
419,591
Latest member
mersanko

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