lock combination exercise

[Bigslim]

OK here is the example I want to create - imagine having a 4digit combination lock, and you want to know all the different combinations it could be. You want them printed seperately so you can see them all.

Now what I am doing involves the same concept only I need to get all the unique combinations where one is not being repeated more than once going backwards or forward.

ie. 0001 = 0010= 0100=1000 all these translate into 1 unique combination.

I want to host a series of games where I have 32 players, 8 teams of 4 players, divided into 4 tables. each week the player combination changes. And having one table be the same as the previous weeks will not work. The series is a 24 week series. And I also can't have the same team members playing at the same table.

I have started with "=index(array,Mrand()+1) after creating a list of all the teams and their players but I'm stuck because, I am going to have to check manually for repeats.

Any ideas would help. Thank you...Rich

 

[TheVillageIdiot]

Here is a brute force VBA example which you may be able to adapt to your needs.

Sub PermutationsBruteForce()
Const kItems As Long = 9
Dim a As Long, b As Long, c As Long, d As Long, RowCount As Long
With ActiveSheet
    For a = 0 To kItems
        For b = 0 To kItems
            For c = 0 To kItems
                For d = 0 To kItems
                    RowCount = RowCount + 1
                    .Cells(RowCount, 1) = a
                    .Cells(RowCount, 2) = b
                    .Cells(RowCount, 3) = c
                    .Cells(RowCount, 4) = d
                Next d
            Next c
        Next b
    Next a
End With
End Sub

 

[shg]

You've got a problem with a name: The Social Golfer Problem. There is no general solution, but there are published results for various configurations. See, for example, http://www.cs.brown.edu/~sello/golf.html and http://www.cs.brown.edu/~sello/solutions.html

EDIT: Also, see http://www.logic.at/prolog/mst.pdf

 

[JackBean]

As shg stated this is the stuff of phd theses.

You state two different problem, lock combination and social golfer.

"And I also can't have the same team members playing at the same table."

This could be social golfer, either 4 groups of 8 with the 8 divided into two teams,
in which case the pairings are counted at each table whether on the same team or not.
Or 8 groups of 4, where the pairings count only if on the same team.

Or there could be another variation of 8 groups of 4,
and also keep track of pairings across teams.
This is more complicated than the social golfer,
and there may be some literature on it but I have not seen it.

So perhaps you should consider what you may be getting into,
so that you can decide whether to tackle this problem,
or tackle something of comparable difficulty,
like maybe stowing away on the first manned mission to Mars.

Consider the 8 groups of 4 problem.

The number of pairings per Group is:
4-1 = 3
6=3+2+1
or:
6=4*(4-1)/2
48=8*6 Pairings per Row

Similarly there are:
496=32*31/2 possible pairings

Cell C1 = =B1/D$1 copy down
Cell D1 = 496

Golf32.xls
	A	B	C	D	E	F	G
1	1	48	0.096774	496	0.000195	6	48
2	2	96	0.193548	496		6
3	3	144	0.290323
4	4	192	0.387097
5	5	240	0.483871
6	6	288	0.580645
7	7	336	0.677419
8	8	384	0.774194
9	9	432	0.870968
10	10	480	0.967742
11	11	528	1.064516
12	12	576	1.16129
13	13	624	1.258065
14	14	672	1.354839
15	15	720	1.451613
16	16	768	1.548387
17	17	816	1.645161
18	18	864	1.741935
19	19	912	1.83871
20	20	960	1.935484
21	21	1008	2.032258
22	22	1056	2.129032
23	23	1104	2.225806
24	24	1152	2.322581
25	25	1200	2.419355
26	26	1248	2.516129
27	27	1296	2.612903
28	28	1344	2.709677
29	29	1392	2.806452
30	30	1440	2.903226
31	31	1488	3
Sheet1

10 Rows is the maximum that has number of pairings of each<= 1 (0.967741935483871).
To get an integer number (3) go 31 Rows.

The solution of 10 Rows in the reference in the previous Post is:

MakePerfect020911-32-4.xls
	A	B	C	D	E	F	G	H
1	01020304	05060708	09101112	13141516	17181920	21222324	25262728	29303132
2	01051326	02080931	03071030	04061528	11202129	12182332	14192427	16172225
3	01071721	02111324	03061822	04101623	05202530	08192832	09142629	12152731
4	01061132	02071425	03081627	04051229	09172430	10192231	13182128	15202326
5	01081824	02121621	03092028	04111727	05142232	06192630	07132331	10152529
6	01091522	02051923	03121726	04081325	06142131	07182729	10202432	11162830
7	01121925	02101826	03111423	04072022	05172831	06162429	08152130	09132732
8	01101428	02062027	03212532	04242631	05091618	07111519	08172329	12132230
9	01162031	02151732	03131929	04141830	05102127	06092325	07122428	08112226
10	01232730	02222829	03051524	04091921	06101317	07162632	08121420	11182531
Sheet3

The pairing grid is:

MakePerfect020911-32-4.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1		01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32
2	01	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
3	02	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
4	03	1	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
5	04	1	1	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
6	05	1	1	1	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
7	06	1	1	1	1	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
8	07	1	1	1	1	1	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
9	08	1	1	1	1	1	1	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
10	09	1	1	1	1	1	1	0	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
11	10	1	1	1	1	1	1	1	0	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
12	11	1	1	1	1	0	1	1	1	1	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
13	12	1	1	1	1	1	0	1	1	1	1	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
14	13	1	1	1	1	1	1	1	1	1	1	1	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
15	14	1	1	1	1	1	1	1	1	1	1	1	1	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
16	15	1	1	1	1	1	1	1	1	1	1	1	1	1	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
17	16	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
18	17	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
19	18	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
20	19	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	X	X	X	X	X	X	X	X	X	X	X	X	X	X
21	20	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	X	X	X	X	X	X	X	X	X	X	X	X	X
22	21	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	X	X	X	X	X	X	X	X	X	X	X	X
23	22	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	X	X	X	X	X	X	X	X	X	X	X
24	23	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	X	X	X	X	X	X	X	X	X	X
25	24	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	X	X	X	X	X	X	X	X	X
26	25	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	X	X	X	X	X	X	X	X
27	26	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	X	X	X	X	X	X	X
28	27	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	X	X	X	X	X	X
29	28	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	X	X	X	X	X
30	29	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	X	X	X	X
31	30	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	X	X	X
32	31	1	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	X	X
33	32	1	1	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	X
Sheet2

The number of Groups of 4 from 32 is 35960=COMBIN(32,4)
The number of combinations of 4 of the Groups is:
69,661,853,445,594,400=COMBIN(35960.4)
But only some of these will have all 32 Characters per Row.
This can be calculated as in:
Find and complete the series of numbers?</SPAN></SPAN>
http://www.mrexcel.com/forum/showthread.php?556229-Find-and-complete-the-series-of-numbers</SPAN>
Post 58.
1,579,413,859,773,750
The number of possible combinations of 10 Rows of these is:
COMBIN(1579413859773750,10) which gives an error.
Use logarithms to calculate.

COMBIN(1579413859773750,10)=LOG(1579413859773750)*10-LOG(FACT(10))
=145.425196414549

So the number of the possible combinations of 10 of the possible Rows is 10 to the 145.425196 power.
10 to the 0.425196 = 2.66
So the number of the possible combinations of 10 of the possible Rows is 2.662E+145.
Only a small fraction will be solutions.

The 10 Rows looks difficult,
but your question calls for going more Rows.

 

[JackBean]

Correction on the # of Combinations:

Cell A1:
=FACT(32)/FACT(28)/FACT(4)
Cell A2:A8:
=FACT(32-ROW(B1)*4)/FACT(28-ROW(B1)*4)/FACT(4)
Cell A9:
=PRODUCT(A1:A8)
Cell A10:
=A9/FACT(8)
=59,287,247,761,257,100,000

The number of possible combinations of 10 Rows of these is:
COMBIN(59287247761257100000,10) which gives an error.
Use logarithms to calculate.

COMBIN(59287247761257100000,10)=LOG(59287247761257100000)*10-LOG(FACT(10))
=191.2

So the number of the possible combinations of 10 of the possible Rows is 10 to the 191.2 power.

Not 10 to the 145.425196 power as in the Previous Post.

To use all 35960=COMBIN(32,4) elements sorted into Rows of 8 Elements,
where all characters appear on each Row,
there are 35960/8 = 4495 Rows.
This is a related variation of the problem that one of the Mrexcel members has worked on.
COMBIN(59287247761257100000,4495)=LOG(59287247761257100000)*4495-LOG(FACT(4495))

LOG(FACT(4495)) gives an error but:
LOG(FACT(4495))={=SUM(LOG(ROW(1:4495)))}

{LOG(59287247761257100000)*4495-SUM(LOG(ROW(1:4495)))}
=74410.37

So the possible combinations of 4495 Rows = 1E+74410.37
This does not consider whether an Element such as 02 07 21 32 can occur on more than one Row, which it cannot.
So the actual possible combinations,
as each Row is selected, is less than that.