Number Generator - No Repeats across but duplicated numerous times down

[K3BAB87]

I am trying to devise a spreadsheet where number generator is used.
I have 3 columns and 42 rows.
In each column, the numbers 15-28 must be used 3 times however going across, it must not be duplicated at all.
How do I do this?

19 28 19
28 17 21
20 17 22
24 20 16
24 28 22

 

[hicksi]

... With difficulty.
If you wonder why it could be hard, consider 3 Sudoku puzzles atop each other. That's the same criteria, and not easily solved.

One not-so-random method is yo have A1=15, B1=16, C1=17, and sequence down. Probably not a "solution"

Another way might be to put 15-28 in cells k1-k14, then copy down twice.
Put =RAND() in columns L1-L42, then sort K:L on L. That gives a randomly-ordered column of 42.
Copy that to A1.
Re-sort. Copy that to B1
Re-sort. Copy that to C1

Then check rows for duplicates. If a failure happens, re-sort and copy again. Eventually you'll get a result.

 

[AlanY]

your first set of 3*14 in column E, anyone help on the other 2 sets?

Excel 2012
	A	B	C	D	E
1	0.015057	28	0.161966	35	23
2	0.352506	25	0.892879	9	17
3	0.42229	24	0.829054	11	21
4	0.918346	15	0.203455	34	16
5	0.729198	19	0.910612	8	22
6	0.889452	16	0.309382	30	25
7	0.444823	23	0.411386	25	21
8	0.548192	22	0.747153	14	27
9	0.811464	17	0.311773	29	28
10	0.643727	20	0.471373	21	23
11	0.619134	21	0.928048	4	15
12	0.745928	18	0.468722	22	22
13	0.239614	26	0.449085	24	20
14	0.19355	27	0.999694	1	28
15		28	0.821478	12	18
16		25	0.107899	38	20
17		24	0.510379	19	19
18		15	0.386178	26	18
19		19	0.85637	10	20
20		16	0.453368	23	17
21		23	0.102123	40	18
22		22	0.291906	31	24
23		17	0.511912	18	15
24		20	0.975996	2	25
25		21	0.33888	28	27
26		18	0.266755	32	15
27		26	0.382782	27	26
28		27	0.706482	16	25
29		28	0.802052	13	26
30		25	0.250594	33	19
31		24	0.052031	42	27
32		15	0.733131	15	28
33		19	0.104523	39	21
34		16	0.475533	20	16
35		23	0.959886	3	24
36		22	0.701531	17	24
37		17	0.13725	36	22
38		20	0.922638	6	16
39		21	0.134003	37	17
40		18	0.926424	5	19
41		26	0.053391	41	26
42		27	0.919354	7	23
Sheet5

Cell Formulas
Range	Formula
A1	=RAND()
B1	=RANK(A1,$A$1:$A$14)+14
B15	=B1
C1	=RAND()
D1	=RANK(C1,$C$1:$C$42)
E1	=INDEX($B$1:$B$42,D1)

 

[MickG]

Try this for Results in Columns "A,B,C"

[COLOR="Navy"]Sub[/COLOR] MG12Aug39
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dic2 [COLOR="Navy"]As[/COLOR] Object, Dic [COLOR="Navy"]As[/COLOR] Object
Randomize
[COLOR="Navy"]Set[/COLOR] Dic2 = CreateObject("scripting.dictionary")

[COLOR="Navy"]Do[/COLOR] Until n = 42
    [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
    c = 0
      ReDim ray(1 To 3)
        [COLOR="Navy"]Do[/COLOR] Until c = 3
            Num = Int(Rnd * 14) + 15
                [COLOR="Navy"]If[/COLOR] Not Dic.exists(Num) [COLOR="Navy"]Then[/COLOR]
                    c = c + 1
                    ray(c) = Num
                    Dic.Add Num, Nothing
                [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Loop[/COLOR]
[COLOR="Navy"]If[/COLOR] Not Dic2.exists(ray(1) & ray(2) & ray(3)) [COLOR="Navy"]Then[/COLOR]
    Dic2.Add ray(1) & ray(2) & ray(3), Nothing
    n = n + 1
    Cells(n, 1) = ray(1): Cells(n, 2) = ray(2): Cells(n, 3) = ray(3)
[COLOR="Navy"]End[/COLOR] If
Loop
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mikerickson]

This is a rather brute force approach.

Sub test()
    Dim anArray() As Long
    Dim oneCell As Range, i As Long
    
    ReDim anArray(1 To 42)
    anArray(1) = 0
    For i = 2 To 42: anArray(i) = (anArray(i - 1) + 1) Mod 14: Next i
    
    With Range("A1:A42")
        .Value = Application.Transpose(anArray)
        .Offset(0, 5).FormulaR1C1 = "=rc1=rc2"
        .Offset(0, 6).FormulaR1C1 = "=rc2=rc3"
        .Offset(0, 7).FormulaR1C1 = "=rc1=rc3"
        .Offset(0, 8).Cells(1, 1).Formula = "=or(" & .Offset(0, 5).Address(, , , True) & ")"
        .Offset(0, 8).Cells(2, 1).Formula = "=or(" & .Offset(0, 5).Resize(, 3).Address(, , , True) & ")"
        
        Do Until .Offset(0, 8).Cells(1, 1).Value = False
            RandomizeArray anArray
            .Offset(0, 1).Value = Application.Transpose(anArray)
        Loop
        Do Until .Offset(0, 8).Cells(2, 1).Value = False
            RandomizeArray anArray
            .Offset(0, 2).Value = Application.Transpose(anArray)
        Loop
        

        With .Offset(0, 5)
            .Resize(, 4).ClearContents
            .Formula = "=RAND()"
        End With
        .Resize(, 6).Sort key1:=.Offset(0, 5), order1:=xlAscending
        .Offset(0, 5).ClearContents
        
        For Each oneCell In .Resize(, 3)
            oneCell.Value = oneCell.Value + 15
        Next oneCell
    End With
    
End Sub

Sub RandomizeArray(ByRef myArray As Variant)
    Dim i As Long, randIndex As Long
    Dim temp As Variant
    Dim Low As Long, High As Long
    Low = LBound(myArray): High = UBound(myArray)
    For i = Low To High
        randIndex = WorksheetFunction.RandBetween(Low, High)
        temp = myArray(i)
        myArray(i) = myArray(randIndex)
        myArray(randIndex) = temp
    Next i
End Sub

 

[AlanY]

ok, this is my humble attempt for a quite interesing problem.
the idea is hit F9 (re-calculate) until Cell M1 = 0 for the 3 set of 3x14 nos without repeat across (Cols J, K & L).

all columns copy formula from Row 1 all the way except Col B, for column B copy B1 to B14 then B15 to B42

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	0.435933	23	0.034367	39	0.643239	23	0.192584	32	FALSE	19	8	21	0
2	0.347439	25	0.528556	18	0.37766	30	0.399974	24	FALSE	22	19	23
3	0.74742	18	0.124604	34	0.770828	12	0.122173	38	FALSE	28	34	37
4	0.505263	22	0.407896	21	0.105924	35	0.153881	35	FALSE	27	13	18
5	0.855405	16	0.87171	10	0.710264	14	0.820335	8	FALSE	20	24	34
6	0.035002	28	0.944558	8	0.85848	9	0.641624	16	FALSE	17	7	3
7	0.128269	27	0.194156	28	0.900788	4	0.257999	31	FALSE	26	35	1
8	0.776331	17	0.242901	26	0.69528	17	0.174329	34	FALSE	15	29	36
9	0.531687	21	0.011232	42	0.876199	7	0.061151	39	FALSE	26	4	10
10	0.608882	20	0.122331	35	0.653337	21	0.608227	17	FALSE	27	25	14
11	0.736784	19	0.1312	32	0.001424	42	0.337566	27	FALSE	22	16	28
12	0.888465	15	0.407151	22	0.256771	34	0.273565	29	FALSE	17	2	5
13	0.368837	24	0.49834	19	0.813207	10	0.00999	41	FALSE	16	21	15
14	0.141859	26	0.791075	12	0.63039	24	0.507957	20	FALSE	15	27	9
15		23	0.142901	30	0.694767	18	0.259504	30	FALSE	25	41	4
16		25	0.210221	27	0.80408	11	0.974748	3	FALSE	24	42	38
17		18	0.027405	40	0.37943	29	0.665209	14	FALSE	15	40	20
18		22	0.092083	37	0.009711	41	0.044105	40	FALSE	21	32	26
19		16	0.972725	4	0.887508	5	0.383196	25	FALSE	22	14	19
20		28	0.12911	33	0.062578	39	0.801194	9	FALSE	16	28	39
21		27	0.981654	3	0.468633	25	0.190376	33	FALSE	18	38	42
22		17	0.70435	13	0.995332	1	0.447394	22	FALSE	24	23	22
23		21	0.591342	16	0.860833	8	0.83884	6	FALSE	25	17	16
24		20	0.960552	6	0.421343	27	0.409771	23	FALSE	28	31	6
25		19	0.988609	1	0.069248	38	0.528284	19	FALSE	23	6	25
26		15	0.133917	31	0.295722	33	0.97628	2	FALSE	18	20	24
27		24	0.98306	2	0.337888	31	0.305334	28	FALSE	25	37	11
28		26	0.832535	11	0.086388	36	0.745444	11	FALSE	19	15	27
29		23	0.960293	7	0.045813	40	0.848137	5	FALSE	27	26	8
30		25	0.114806	36	0.677799	19	0.927379	4	FALSE	17	5	35
31		18	0.019165	41	0.078833	37	0.985207	1	FALSE	24	3	32
32		22	0.473777	20	0.643949	22	0.450389	21	FALSE	28	1	33
33		16	0.970694	5	0.662374	20	0.002966	42	FALSE	16	39	13
34		28	0.285971	25	0.924306	2	0.142633	36	FALSE	19	30	7
35		27	0.564346	17	0.724816	13	0.588749	18	FALSE	18	10	40
36		17	0.040671	38	0.701803	15	0.834901	7	FALSE	20	18	31
37		21	0.699678	14	0.910473	3	0.712323	12	FALSE	26	12	29
38		20	0.29461	24	0.877036	6	0.126733	37	FALSE	20	9	12
39		19	0.155956	29	0.419107	28	0.74646	10	FALSE	23	36	17
40		15	0.879115	9	0.457937	26	0.353677	26	FALSE	21	33	2
41		24	0.31007	23	0.316772	32	0.663586	15	FALSE	21	22	30
42		26	0.658189	15	0.696812	16	0.704499	13	FALSE	23	11	41
Sheet5

Cell Formulas
Range	Formula
A1	=RAND()
B1	=RANK(A1,$A$1:$A$14)+14
B15	=B1
C1	=RAND()
D1	=RANK(C1,$C$1:$C$42)
E1	=RAND()
F1	=RANK(E1,$E$1:$E$42)
G1	=RAND()
H1	=RANK(G1,$G$1:$G$42)
I1	=OR(J1=K1,J1=L1,K1=L1)
J1	=INDEX($B$1:$B$42,D1)
K1	=INDEX($F$1:$F$42,F1)
L1	=INDEX($H$1:$H$42,H1)
M1	=COUNTIF(I1:I42,TRUE)

 

[hicksi]

This is a rather brute force approach.

My brute force approach iterated 300 times before success.

 

[AlanY]

ok, this is my humble attempt for a quite interesing problem.
the idea is hit F9 (re-calculate) until Cell M1 = 0 for the 3 set of 3x14 nos without repeat across (Cols J, K & L).

all columns copy formula from Row 1 all the way except Col B, for column B copy B1 to B14 then B15 to B42

it can be done.

4-35-8-6-5

no of times to hit F9 to get results

 

[wideboydixon]

Interestingly, even this brute force solution is missing some functionality. The way the original problem was posed, I think you have to imagine have three bags with 42 balls (3 x 15, 3 x 16, ... 3 x 28) in each. You then draw one ball from each bag and hopefully you don't end up with any duplicates. If you do, you put the balls back in and try again. Of course, you still might end up with an unsolveable case when you get near the end (e.g. you're left with 28, 15, 28 in the three bags) so in that case you'd have to start again.

I think it should be possible, for example, to start with the following rows:

15, 16, 17
15, 18, 20
15, 28, 30

I thought of a way to do it in code but I haven't got around to writing it up.

WBD

 

[mikerickson]

My brute force approach iterated 300 times before success.

my logic is similar

Put three copies of 0-14 in A

do
put 3 copies of 0-14 in B
rand re-order b
loop until different than a

do
put 3 copies of 0-14 in C
rand re-order C
loop until different than a or b

rand re-order A:C
Add 15 to everything.

The numbers of loops before success were in the 600 range, but each loop was of only one column so they were shorter than a "test and re-do everything" loop