GRID 3x3, row, column, total must be 14

[motilulla]

Hello,

Is it possible to create all possible Grids 3x3 which rows and column total must be 14 using all numbers are in column B, C, D

Apart of creating all possible Grids could be having random option too.

Example below showing 4 examples not necessary show the total of rows and columns it is shown just for example

Book1
	A	B	C	D	E	F	G	H	I	J
1		Choice 1	Choice 2	Choice 3
2		0	5	9
3		0	6	8
4		0	7	7
5		0	8	6		0	5	9	14
6		0	9	5		6	4	4	14
7		1	4	9		8	5	1	14
8		1	5	8		14	14	14
9		1	6	7
10		1	7	6		6	5	3	14
11		1	8	5		3	3	8	14
12		1	9	4		5	6	3	14
13		2	3	9		14	14	14
14		2	4	8
15		2	5	7		9	2	3	14
16		2	6	6		3	5	6	14
17		2	7	5		2	7	5	14
18		2	8	4		14	14	14
19		2	9	3
20		3	2	9		5	7	2	14
21		3	3	8		3	4	7	14
22		3	4	7		6	3	5	14
23		3	5	6		14	14	14
24		3	6	5
25		3	7	4
26		3	8	3
27		3	9	2
28		4	1	9
29		4	2	8
30		4	3	7
31		4	4	6
32		4	5	5
33		4	6	4
34		4	7	3
35		4	8	2
36		4	9	1
37		5	0	9
38		5	1	8
39		5	2	7
40		5	3	6
41		5	4	5
42		5	5	4
43		5	6	3
44		5	7	2
45		5	8	1
46		5	9	0
47		6	0	8
48		6	1	7
49		6	2	6
50		6	3	5
51		6	4	4
52		6	5	3
53		6	6	2
54		6	7	1
55		6	8	0
56		7	0	7
57		7	1	6
58		7	2	5
59		7	3	4
60		7	4	3
61		7	5	2
62		7	6	1
63		7	7	0
64		8	0	6
65		8	1	5
66		8	2	4
67		8	3	3
68		8	4	2
69		8	5	1
70		8	6	0
71		9	0	5
72		9	1	4
73		9	2	3
74		9	3	2
75		9	4	1
76		9	5	0
77
78
79
80
81
Sheet2

Thank you all
Excel 2000
Regards,
Moti

 

[MickG]

This may be a starting point:-
NB:- Matrix based on column "A" data"
https://app.box.com/s/3c4kia2hs7w8fmbda19wpn2va17laujo

 

[motilulla]

This may be a starting point:-
NB:- Matrix based on column "A" data"
https://app.box.com/s/3c4kia2hs7w8fmbda19wpn2va17laujo

Thank you MickG, for giving a code solution.

I saved workbook as workbook 97-2003 as I am using version 2000
Clicking on update button highlight following line is shown in red, gives error 438

 Dim K As Variant, Pk As Long, c As Long, Num As Long, temp As Long, Rw As Long
  Dim Myray As Variant
   temp = -1
   For Rw = 1 To UBound(nRay, 1)
    [COLOR=#ff0000][B]Num = Application.RandBetween(0, .Count - 1)[/B][/COLOR]

Kind Regards,
Moti

 

[MickG]

Try this:-
https://app.box.com/s/fqqe89diw8tfftsus7p9c3cbc7cnn3up

 

[motilulla]

Try this:-
https://app.box.com/s/fqqe89diw8tfftsus7p9c3cbc7cnn3up

MickG, this one is perfect!! Each time clicked it create a different grid. I like the random grid solution it is fine.

I imagine would be difficult to have all as per list #post1 , even don't know how much set could be made. May be thousands?

Thank you for your help

kind Regards,
Moti

 

[MickG]

If your idea was to use up all the numbers in your data to make up Magic squares, then I can see a problem with matching/using all the numbers to make individual 3x3 squares. That's a bit too difficult!!
You could use most of them, but there is bound to be a number left over.

 

[motilulla]

If your idea was to use up all the numbers in your data to make up Magic squares, then I can see a problem with matching/using all the numbers to make individual 3x3 squares. That's a bit too difficult!!
You could use most of them, but there is bound to be a number left over.

Hello Mick, I think it's possible some number left over, and it's difficult too get all magic esquires also.

I was just trying to find how much could be all matches, but I am not sure, for example looking in to column B "0 to 9" there are total 75, as shown in F4:G13, so I imagine could be total magic esquire 330 as shown in Q4:Q13 with each digit "0 to 9", may I am wrong.

Here are the example calculations

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Choice 1	Choice 2	Choice 3													Total
2		0	5	9			75										330
3		0	6	8		With	Match
4		0	7	7		0	5	4	3	2	1						15
5		0	8	6		1	6	5	4	3	2	1					21
6		0	9	5		2	7	6	5	4	3	2	1				28
7		1	4	9		3	8	7	6	5	4	3	2	1			36
8		1	5	8		4	9	8	7	6	5	4	3	2	1		45
9		1	6	7		5	10	9	8	7	6	5	4	3	2	1	55
10		1	7	6		6	9	8	7	6	5	4	3	2	1		45
11		1	8	5		7	8	7	6	5	4	3	2	1			36
12		1	9	4		8	7	6	5	4	3	2	1				28
13		2	3	9		9	6	5	4	3	2	1					21
14		2	4	8
15		2	5	7
16		2	6	6
17		2	7	5
18		2	8	4
19		2	9	3
20		3	2	9
21		3	3	8
22		3	4	7
23		3	5	6
24		3	6	5
25		3	7	4
26		3	8	3
27		3	9	2
28		4	1	9
29		4	2	8
30		4	3	7
31		4	4	6
32		4	5	5
33		4	6	4
34		4	7	3
35		4	8	2
36		4	9	1
37		5	0	9
38		5	1	8
39		5	2	7
40		5	3	6
41		5	4	5
42		5	5	4
43		5	6	3
44		5	7	2
45		5	8	1
46		5	9	0
47		6	0	8
48		6	1	7
49		6	2	6
50		6	3	5
51		6	4	4
52		6	5	3
53		6	6	2
54		6	7	1
55		6	8	0
56		7	0	7
57		7	1	6
58		7	2	5
59		7	3	4
60		7	4	3
61		7	5	2
62		7	6	1
63		7	7	0
64		8	0	6
65		8	1	5
66		8	2	4
67		8	3	3
68		8	4	2
69		8	5	1
70		8	6	0
71		9	0	5
72		9	1	4
73		9	2	3
74		9	3	2
75		9	4	1
76		9	5	0
77
78
79
80
81
Sheet2

Kind Regards,
Moti