Generate 7 random out of 9 number limiting max repeated 2 or 3

motilulla · Nov 6, 2018

Hello,

Here is a bit difficult random number generator task, because I need a VBA that can choose 7 random numbers out of 9, which are listed in the cells A4:A12 

Create for example 52 random in the cells F4:L55 but conditions is limit max repetitions 2 or 3 as shown in the example below is it possible?

Finally in the column N list the repeated numbers amount

Example...

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

2

3

Total Numbers

n1

n2

n3

n4

n5

n6

n7

Max Repeated

4

1

3

1

5

7

1

3

2

5

2

8

5

8

1

5

6

1

2

6

3

1

5

7

1

3

2

7

4

5

2

5

3

4

5

1

3

8

5

2

1

5

2

8

1

3

9

6

8

7

1

5

1

4

2

10

7

3

4

1

4

1

3

11

8

2

6

2

1

4

1

3

2

12

9

1

4

8

3

8

2

8

3

13

2

3

1

3

4

3

14

3

6

4

8

3

1

8

2

15

1

3

5

3

1

5

3

16

3

2

3

1

4

1

8

2

17

1

5

8

4

3

6

2

18

1

4

5

2

4

1

3

2

19

5

3

2

1

4

1

4

2

20

1

4

1

5

8

3

21

3

4

1

5

2

1

2

22

1

9

2

9

5

6

3

23

4

9

2

3

24

1

4

8

2

25

1

4

5

3

5

6

2

26

1

2

1

2

1

3

4

3

27

2

1

5

2

8

2

5

3

28

9

1

3

6

4

5

3

2

29

2

4

1

3

30

5

1

7

2

3

2

3

2

31

2

6

1

7

1

3

7

2

32

3

1

4

8

3

33

6

1

8

1

4

3

1

3

34

3

5

8

3

4

2

35

1

2

3

7

4

2

3

2

36

8

3

2

1

8

1

3

37

7

5

3

2

4

5

2

38

8

4

3

5

1

5

2

39

8

2

1

3

5

2

40

4

2

5

1

3

41

3

5

6

8

5

3

42

3

2

7

2

9

1

2

43

3

4

7

1

2

6

2

44

5

6

2

8

2

3

7

2

45

9

1

3

6

9

1

9

3

46

2

7

4

8

1

7

2

47

6

3

1

4

2

8

2

48

5

3

1

5

2

8

2

49

3

9

3

2

1

3

50

5

1

9

1

9

8

4

2

51

2

5

3

4

2

9

2

3

52

2

1

8

1

2

1

3

53

3

1

3

4

1

5

8

2

54

1

4

3

2

3

55

2

1

4

3

8

6

2

Sheet10

Thank you all

Excel 2000
Regards,
Moti

motilulla · Nov 6, 2018

Hello, just noticed in the some of rows there are 2 or 3 repeated but may be 3 numbers 2 times also it is ok also no problem 

Like for example in the 
Row 4 there is 1 a 2 times & 5 is also a 2 times
Row 5 there is 1 a 2 times, 5 is also a 2 times & 8 is also a 2 times
Row 6 there is 1 a 2 times, 3 is also a 2 times & 5 is also a 2 times

And it could be find in other rows may be... and it is ok too

Regards,
Moti

motilulla · Nov 6, 2018

Hello, it would be better to define in a simple way that any "among 9 num" single number cannot be appeared 4 times in the row (in other words limit for single number is 2 or 3 times max in the row) I hope may this is more clearer now

Regards,
Moti

MickG · Nov 6, 2018

Try this:-
The first Piece of code is run from "CommandButton1"
Change to a Sub if required !!!

Code:

Option Explicit
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Rw [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRay [COLOR="Navy"]As[/COLOR] Variant, oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("F4:L55")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Rw [COLOR="Navy"]In[/COLOR] Rng.Rows
  nRay = nRw: oMax = 0
  [COLOR="Navy"]For[/COLOR] n = 1 To UBound(nRay)
        oMax = Application.Max(Dic(nRay(n, 1)), oMax)
        [COLOR="Navy"]If[/COLOR] Dic(nRay(n, 1)) > 3 [COLOR="Navy"]Then[/COLOR]
            oMax = 0
            [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]Next[/COLOR] n
       [COLOR="Navy"]If[/COLOR] oMax > 0 [COLOR="Navy"]Then[/COLOR]
            Rw.Cells(1).Offset(, 8).Value = oMax
            Rw.Value = Application.Transpose(nRay)
       [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Rw

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


Function nRw() [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
ReDim Ray(1 To 9, 1 To 1)
    [COLOR="Navy"]For[/COLOR] Ac = 1 To 7
        Num = Application.RandBetween(1, 9)
        Ray(Ac, 1) = Num
        Dic(Num) = Dic(Num) + 1
    [COLOR="Navy"]Next[/COLOR] Ac
nRw = Ray
[COLOR="Navy"]End[/COLOR] Function

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Rick Rothstein · Nov 6, 2018

Here is a macro solution that you can consider...

Code:

[table="width: 500"]
[tr]
	[td]Sub RandomNumbersLimited()
  Dim R As Long, C As Long, MaxRepts As Long, HowMany As Long, Rand As Long
  Dim Nums As Variant, Result As Variant, Maxes As Variant, Used() As Long
  Randomize
  MaxRepts = 3
  HowMany = 52
  ReDim Result(1 To HowMany, 1 To 7)
  Nums = Range("A4", Cells(Rows.Count, "A").End(xlUp))
  ReDim Maxes(1 To HowMany, 1 To 1)
  For R = 1 To HowMany
    ReDim Used(1 To UBound(Nums))
    For C = 1 To 7
      Do
        Rand = Int((Nums(UBound(Nums), 1) - Nums(1, 1) + 1) * Rnd + Nums(1, 1))
        If Used(Rand) < MaxRepts Then
          Used(Rand) = Used(Rand) + 1
          Result(R, C) = Rand
          Exit Do
        End If
      Loop
    Next
    Maxes(R, 1) = Application.Max(Used)
  Next
  Range("F4:L4").Resize(UBound(Result)) = Result
  Range("N4").Resize(UBound(Maxes)) = Maxes
End Sub[/td]
[/tr]
[/table]

Edit Note: While you specifically asked for 9 total numbers to randomly distribute, my code will work if you wanted to extend that list, say, to 20 number (although you will get less repeats, of course).

motilulla · Nov 6, 2018

MickG said:
Try this:-
The first Piece of code is run from "CommandButton1"
Change to a Sub if required !!!
Regards Mick

Thank you Mick, code were giving an error at the line shown in red, just changed the line shown in green it worked fine, doing this I find the code generate lines with max 1 also could it be restricted Min repeated to 2, and Max repeated to 3

Code:

</SPAN></SPAN>
Changed this line</SPAN></SPAN>
[COLOR=#ff0000]Num = Application.Randbetween(1, 9) [/COLOR]</SPAN></SPAN>
 
This one </SPAN></SPAN>
[COLOR=#006400]Num = Int(Rnd * 9) + 1[/COLOR]</SPAN></SPAN>

Regards,
Moti

motilulla · Nov 6, 2018

Rick Rothstein said:
Here is a macro solution that you can consider...
Edit Note: While you specifically asked for 9 total numbers to randomly distribute, my code will work if you wanted to extend that list, say, to 20 number (although you will get less repeats, of course).

Thank you Rick, code worked fine!! it is generating lines with max 1 also, can be restricted Min repeated to 2, and Max repeated to 3

Regards,
Moti

Rick Rothstein · Nov 6, 2018

motilulla said:
Thank you Mick, code were giving an error at the line shown in red, just changed the line shown in green it worked fine, doing this I find the code generate lines with max 1 also could it be restricted Min repeated to 2, and Max repeated to 3 

Code:

 Changed this line [COLOR=#ff0000]Num = Application.Randbetween(1, 9) [/COLOR] This one [COLOR=#006400]Num = Int(Rnd * 9) + 1[/COLOR]


Regards,
Moti

Just so Mick knows why that line of code he used failed for you... it is because you are using Excel 2000.

Rick Rothstein · Nov 6, 2018

motilulla said:
Thank you Rick, code worked fine!! it is generating lines with max 1 also, can be restricted Min repeated to 2, and Max repeated to 3

Regards,
Moti

Ah, I did not understand from your original post that you wanted to force a repeat of 2 at minimum. I am not sure the method I employed will let me to that easily or not. Let me think about it and if I can modify my code to do that, I'll post it back here.

motilulla · Nov 6, 2018

Rick Rothstein said:
Ah, I did not understand from your original post that you wanted to force a repeat of 2 at minimum. I am not sure the method I employed will let me to that easily or not. Let me think about it and if I can modify my code to do that, I'll post it back here.

Thank you Rick, and sorry it is my mistake, I did not clarified force a repeat of 2 at minimum. Just said Maximum repeated should be 3 

Regards,
Moti

Generate 7 random out of 9 number limiting max repeated 2 or 3

motilulla

Well-known Member

Excel Facts

motilulla

Well-known Member

motilulla

Well-known Member

MickG

MrExcel MVP

Rick Rothstein

MrExcel MVP

motilulla

Well-known Member

motilulla

Well-known Member

Rick Rothstein

MrExcel MVP

Rick Rothstein

MrExcel MVP

motilulla

Well-known Member

Similar threads

Forum statistics

Share this page

Generate 7 random out of 9 number limiting max repeated 2 or 3

Well-known Member

Excel Facts

Well-known Member

Well-known Member

MrExcel MVP

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock