Generate random "set of 14" out of "3-digits"

[Kishan]

Using Excel 2000

Hi,

I want VBA the could generate "set of 14" out of "3-digits"

My 3-digit set is in the A2:A4 consists of "1X2"
In the cells A6:A45 filled number 1 to 40 this mean I need 40 random sets or it can be asked via input "how many set required"

First "set of 14" is generated in the B6:O6
Second "set of 14" is generated in the B7:O7 and continue till B45:O45

Example sheet...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	My Set
2	1
3	X
4	2
5	Generate	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12	C13	C14
6	1	1	1	2	X	1	1	1	1	1	2	1	1	X	1
7	2	2	1	2	X	2	2	1	2	1	2	X	X	2	1
8	3	1	1	1	2	2	1	2	2	1	1	X	1	2	2
9	4	1	2	1	2	1	1	1	2	1	1	1	1	X	1
10	5	1	2	1	2	1	1	2	X	1	X	2	1	2	X
11	6	2	1	1	1	X	1	2	X	2	2	1	2	X	2
12	7	X	2	1	1	2	X	1	X	1	1	X	2	2	2
13	8	2	1	1	2	1	1	1	1	X	1	X	1	1	1
14	9	X	2	2	2	1	2	1	X	2	X	1	X	2	1
15	10	1	1	X	1	2	1	1	X	1	1	1	2	2	1
16	11	2	1	X	X	1	1	2	2	X	1	2	1	1	1
17	12	2	X	2	X	1	1	2	X	X	2	1	1	1	X
18	13	1	1	2	2	X	2	2	1	1	1	1	1	2	1
19	14	X	1	1	2	2	1	1	X	X	1	X	1	1	X
20	15	2	1	1	2	X	1	2	X	X	2	1	1	2	1
21	16	1	X	2	1	2	X	2	1	X	1	2	X	X	2
22	17	X	2	2	1	2	1	X	1	1	1	1	1	2	X
23	18	1	2	2	X	2	1	2	1	X	1	2	2	2	X
24	19	1	1	2	1	1	2	1	1	1	1	1	1	2	1
25	20	2	1	1	X	X	2	X	1	X	X	1	1	1	X
26	21	X	1	X	1	2	1	2	X	1	X	1	X	X	2
27	22	1	X	1	2	X	X	X	1	1	1	1	1	1	2
28	23	X	2	2	X	2	1	1	1	1	X	1	1	1	2
29	24	X	1	1	2	1	2	1	1	2	2	1	X	1	X
30	25	2	1	X	X	2	2	2	2	X	1	X	2	1	X
31	26	2	1	2	1	1	2	2	2	1	2	2	X	1	X
32	27	1	X	X	1	1	1	X	X	2	1	X	2	X	1
33	28	2	1	2	X	X	1	X	1	1	1	2	2	X	2
34	29	2	2	2	1	1	1	X	1	1	1	2	X	X	2
35	30	1	1	1	X	2	2	2	1	1	2	2	2	X	1
36	31	1	X	1	1	2	1	2	1	2	2	X	1	2	X
37	32	1	1	2	1	1	X	1	X	2	X	1	1	X	2
38	33	2	2	1	X	1	2	2	1	2	1	2	1	2	2
39	34	2	X	X	X	2	1	2	X	2	1	2	2	2	X
40	35	2	1	X	2	2	2	2	1	X	X	1	X	X	X
41	36	1	2	X	X	2	1	2	1	2	1	1	2	1	2
42	37	X	X	1	1	2	1	1	1	X	1	X	2	X	2
43	38	1	1	2	2	1	1	2	X	2	X	X	2	1	1
44	39	X	2	1	1	2	2	1	2	1	1	1	1	1	1
45	40	1	1	2	X	1	1	X	X	1	1	2	2	1	2
46
47
48
Generate Random 1X2

Thank you in advance

Regards,
Kishan

 

[mikerickson]

Would the formula =INDEX($A$2:$A$4, RANDBETWEEN(1,3), 1) put in B6 and dragged down and right do what you want?

 

[Kishan]

Would the formula =INDEX($A$2:$A$4, RANDBETWEEN(1,3), 1) put in B6 and dragged down and right do what you want?

Hi mikerickson, yes your formula is doing the perfect job.

Thank you for your help

Regards,
Kishan

 

[Kishan]

Hi again,

Could it is possible to limit "X & 2" counts in each "set of 14" between 5 to 9, I mean do not show the sets have counts less the 5 and grater than 9 of "X & 2"

These are the formula results, which make sets, makes count of "X & 2" without limits

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	My Set
2	1
3	X
4	2															Count
5	Generate	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12	C13	C14	X & 2
6	1	1	X	1	2	X	X	X	X	1	X	1	1	2	X	9
7	2	X	2	X	X	2	2	1	1	X	1	1	1	2	X	9
8	3	1	1	2	2	1	X	2	1	1	1	2	1	1	X	6
9	4	1	X	2	1	1	2	X	2	X	2	1	1	2	2	9
10	5	X	1	1	2	1	X	X	2	2	1	1	X	1	1	7
11	6	2	2	X	X	2	1	2	2	2	1	X	1	1	X	10
12	7	2	X	2	1	X	1	2	1	1	2	1	1	1	2	7
13	8	1	X	2	X	1	1	1	X	2	1	1	2	2	2	8
14	9	X	1	1	2	1	1	2	X	2	X	2	2	X	X	10
15	10	X	2	X	2	2	2	X	2	1	X	X	2	1	2	12
16	11	1	1	X	1	X	X	X	1	1	1	1	1	2	1	5
17	12	X	1	1	X	2	1	X	1	X	1	X	1	2	X	8
18	13	1	X	2	1	X	X	2	2	1	2	2	2	X	2	11
19	14	2	1	1	1	X	2	X	1	2	2	2	2	1	X	9
20	15	1	2	1	2	X	2	1	2	1	2	1	X	2	1	8
21	16	X	2	2	X	2	2	1	2	1	X	2	2	X	1	11
22	17	2	X	1	1	X	X	2	X	1	X	X	X	1	2	10
23	18	X	2	X	2	2	X	1	X	X	2	2	2	X	2	13
24	19	X	1	1	X	1	2	1	2	1	1	X	1	2	2	7
25	20	X	X	2	X	X	1	1	X	2	1	2	X	X	1	10
26	21	2	1	2	2	2	2	1	2	1	X	2	1	X	1	9
27	22	1	X	1	X	X	X	X	X	1	2	X	2	2	X	11
28	23	2	X	2	2	2	1	2	1	X	2	1	2	2	X	11
29	24	2	1	X	1	1	X	X	X	X	X	X	2	2	1	10
30	25	1	2	2	2	2	2	X	X	X	2	X	2	X	X	13
31	26	2	2	X	1	2	X	X	X	X	1	X	X	2	X	12
32	27	2	2	X	2	1	2	1	1	2	1	1	2	2	X	9
33	28	2	1	X	1	X	X	2	1	X	X	1	X	X	1	9
34	29	2	1	X	1	2	X	2	X	X	1	2	X	2	X	11
35	30	1	1	1	1	2	2	1	2	X	X	2	2	1	2	8
36	31	X	1	1	1	X	X	1	1	2	X	X	1	2	1	7
37	32	2	2	1	2	1	X	2	1	X	1	X	2	2	1	9
38	33	2	X	1	1	1	X	1	2	X	1	1	X	2	2	8
39	34	X	2	2	2	2	1	X	2	1	2	2	2	X	2	12
40	35	2	2	2	X	X	X	2	1	1	2	1	1	X	2	10
41	36	1	2	X	X	X	1	1	X	X	2	X	1	1	2	9
42	37	2	X	1	X	1	X	X	X	X	1	2	X	1	2	10
43	38	1	1	1	2	2	2	1	1	X	X	X	2	2	1	8
44	39	2	1	2	2	X	X	2	X	X	1	X	2	X	1	11
45	40	1	2	X	2	2	X	X	2	X	X	X	X	1	2	12
46
47
48
Generate Random 1X2

Regards,
Kishan

 

[Kishan]

Hi,

Here is the example in which counts of the " X & 2" are limits between 5 to 9 may be VBA solution if it is not possible by the formula

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	My Set
2	1
3	X
4	2															Count
5	Generate	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12	C13	C14	X & 2
6	1	1	2	X	1	2	1	2	1	X	2	1	X	1	X	8
7	2	X	1	X	2	1	1	1	X	1	X	2	2	2	1	8
8	3	2	1	1	1	X	1	X	1	X	2	2	1	1	X	7
9	4	1	1	1	1	X	2	1	X	2	1	1	X	1	X	6
10	5	X	1	1	1	X	X	1	2	2	2	1	1	1	1	6
11	6	2	2	X	X	X	1	2	1	1	1	1	2	1	X	8
12	7	X	1	1	2	X	2	2	2	1	1	2	X	1	1	8
13	8	1	1	X	1	1	X	X	X	1	2	X	2	2	X	9
14	9	1	2	1	1	2	2	2	1	1	1	1	1	X	X	6
15	10	X	1	X	1	1	2	1	2	2	1	2	1	1	1	6
16	11	1	1	X	1	1	2	2	2	1	1	X	1	1	X	6
17	12	2	1	1	1	X	2	X	2	1	X	X	X	2	1	9
18	13	1	X	1	2	1	X	1	1	1	1	X	1	X	1	5
19	14	X	2	1	2	2	1	X	1	2	1	2	2	1	1	8
20	15	1	1	1	X	X	X	1	1	1	1	X	X	1	X	6
21	16	1	2	X	1	2	1	2	1	X	1	2	2	2	X	9
22	17	2	1	X	1	1	1	1	X	1	1	X	1	X	1	5
23	18	2	1	1	X	2	X	1	1	X	1	2	X	2	X	9
24	19	X	1	2	2	2	1	X	X	2	1	1	1	X	1	8
25	20	1	2	1	1	X	1	2	2	X	X	2	X	1	X	9
26	21	1	1	1	X	X	1	1	2	1	1	X	1	X	1	5
27	22	X	1	2	X	2	1	1	1	1	1	1	X	1	X	6
28	23	X	2	2	1	1	2	1	1	2	X	1	X	X	X	9
29	24	1	1	1	2	2	1	2	X	1	X	1	2	2	1	7
30	25	X	X	X	1	1	X	X	2	1	1	1	2	X	2	9
31	26	1	2	X	X	1	X	2	1	1	X	1	1	1	X	7
32	27	1	X	1	X	2	1	1	2	X	1	2	1	1	1	6
33	28	1	2	1	X	1	1	2	1	X	1	2	1	1	2	6
34	29	1	1	2	1	2	2	1	1	2	2	1	1	1	2	6
35	30	1	1	1	1	2	2	2	1	X	X	X	2	X	1	8
36	31	2	1	2	X	1	1	X	1	X	X	X	2	2	1	9
37	32	1	1	1	1	X	X	2	1	X	1	X	1	2	1	6
38	33	1	X	2	2	X	2	1	2	1	X	1	1	X	1	8
39	34	X	1	2	1	1	1	1	X	1	1	X	1	X	X	6
40	35	1	1	1	2	2	1	2	2	1	1	X	1	2	2	7
41	36	1	2	1	2	1	1	2	X	1	X	2	1	2	X	8
42	37	2	1	1	1	X	1	2	X	2	2	1	2	X	2	9
43	38	X	2	1	1	2	X	1	X	1	1	X	2	2	2	9
44	39	1	1	X	1	2	1	1	X	1	1	1	2	2	1	5
45	40	2	1	X	X	1	1	2	2	X	1	2	1	1	1	7
46
47
48
Generate Random 1X2-1

Regards,
Kishan

 

[Rick Rothstein]

Hi,

Here is the example in which counts of the " X & 2" are limits between 5 to 9 may be VBA solution if it is not possible by the formula

Is there a minimum number of X's or 2's that you would want to maintain as well?

 

[Kishan]

Is there a minimum number of X's or 2's that you would want to maintain as well?

Hi Rick Rothstein, Thank you for your reply I want random X & 2, also yes if it could be possible to maintain minimum number of X's or 2's will be the great idea.

Regards,
Kishan

 

[Rick Rothstein]

Hi Rick Rothstein, Thank you for your reply I want random X & 2, also yes if it could be possible to maintain minimum number of X's or 2's will be the great idea.

Uh, what would that minimum number be?

 

[Kishan]

Uh, what would that minimum number be?

Hi Rick Rothstein, it would be bit complicate, as I want total of X & 2 "between 5 to 9" so I need to limit for example for "X & 2" if count are 5 it could be then...
X = 0 & 2 = 5
X = 1 & 2 = 4
X = 2 & 2 = 3
X = 3 & 2 = 2
X = 1 & 2 = 4
X = 0 & 2 = 5
It would be for the "X & 2" count different pattern for 6, 7, 8 & 9 don't know how could be managed did you got any idea please suggest

Thank you

Regards,
Kishan

 

[Rick Rothstein]

Give this code a try (you run the RandomCharacterSets macro (it will call the RandomizeArray function as it needs to)...

[table="width: 500"]
[tr]
	[td]Sub RandomCharacterSets()
  Dim X As Long, Z As Long, LowerLimit As Long, UpperLimit As Long, HowManySets As Long, ColCount As Long, X2count As Long, RowSet As String
  LowerLimit = 5
  UpperLimit = 9
  HowManySets = 40
  ColCount = 14
  For X = 1 To HowManySets
    X2count = Application.RandBetween(LowerLimit, UpperLimit)
    RowSet = String(ColCount - X2count, "1")
    For Z = 1 To X2count
      RowSet = RowSet & Choose(Application.RandBetween(1, 2), "X", "2")
    Next
    Cells(5 + X, "A").Value = X
    Cells(5 + X, "B").Resize(, ColCount) = RandomizeArray(Split(Trim(Replace(StrConv(RowSet, vbUnicode), Chr(0), " "))))
  Next
End Sub

Function RandomizeArray(ByVal ArrayIn As Variant) As Variant
  Dim cnt As Long, RandomIndex As Long, tmp As Variant
  Randomize
  If VarType(ArrayIn) >= vbArray Then
    For cnt = UBound(ArrayIn) To LBound(ArrayIn) Step -1
      RandomIndex = Int((cnt - LBound(ArrayIn) + 1) * Rnd + LBound(ArrayIn))
      tmp = ArrayIn(RandomIndex)
      ArrayIn(RandomIndex) = ArrayIn(cnt)
      ArrayIn(cnt) = tmp
    Next
  End If
  RandomizeArray = ArrayIn
End Function
[/td]
[/tr]
[/table]