Running a Macro Until result Achieved

[PM1]

I have a table shown A2:F18 below that Randomises each of the 4 columns A-D with No's from 1 - 17by pressing F9 - this is done in a the following formula:-

=LARGE(ROW($1:$18)*NOT(COUNTIF(A$1:A1, ROW($1:$18))), RANDBETWEEN(1,18-ROW(A1)))

I have another column F which validates if the row is unique and has no duplicates and shows 1 or 0 and has another long winded formula:-

=IF(A2=B2,0,IF(A2=C2,0,IF(A2=D2,0,IF(B2=C2,0,IF(B2=D2,0,IF(C2=D2,0,1))))))

At the bottom of the table I have a cell that checks if column F = 17, being all unique rows and reports NOT YET or SUCCESS, SUCCESS has not yet been reached and my F9 finger is sore

I need to be able to run this in a Macro until the result is = 17, any ideas?

Excel Workbook
	A	B	C	D	E	F	G	H	I
2	7	3	4	15	*	1	*	*	*
3	17	18	14	13	*	1	*	*	*
4	11	8	8	2	*	0	*	*	*
5	6	11	17	7	*	1	*	*	*
6	12	12	18	5	*	0	*	*	*
7	3	6	2	12	*	1	*	*	*
8	4	9	13	9	*	0	*	*	*
9	18	10	7	16	*	1	*	*	*
10	13	2	11	11	*	0	*	*	*
11	15	13	5	6	*	1	*	*	*
12	8	5	12	8	*	0	*	*	*
13	14	14	15	14	*	0	*	*	*
14	5	7	16	3	*	1	*	*	*
15	9	17	6	18	*	1	*	*	*
16	2	4	10	17	*	1	*	*	*
17	16	15	3	10	*	1	*	*	*
18	10	16	9	4	*	1	*	*	*
19	*	*	*	*	*	*	*	*	NOT YET
Sheet1

 

[PM1]

It generates duplicates in the column but not in the row, it needs to be unique in column and row

 

[baitmaster]

please can you clarify your exact requirement then

you want 12 unique sets of 3 numbers chosen from 1-17. There are 680 possible combinations of numbers that meet this criteria
[17! / (14! * 3!)]

separately you want 8 unique sets of 4 numbers chosen from 1-17. There are 2380 possible combinations of numbers that meet this criteria
[17!/ (13! * 4!)]

Is it ok if a set of 4 is identical to any of the sets of 3, but with one additional value added, i.e. any set of 3 is a subset of any set of 4?

You might consider generating two complete sets of all possible unique combinations, then selecting your sets at random, again testing for uniqueness. The code to select which combination would be similar to my previous code (using e.g. 680 as a row number, instead of 17)

To generate all combinations, see my recent postings on a related subject here

 

[PM1]

Ok, I need 20 groups in total, 12 of which are made up of 3 No's and 8 groups which are made up of 4 no's. This is spread over 4 days. So Day 1 is 3 x 3 and 2 x 4, so on for the next 3 days.

The numbers I substitute for Names. So I have 17 Names which I need to allocate into each of the table.

Each day the groups are random and that is easy to generate as you have shown. What I'm trying to achieve is that the groups on Day 2 are unique from Day 1, and on Day 3 they are unique from Days 1&2 and so on for day 4.

So I end up with 20 total groups all unique

 

[baitmaster]

OK I got something which seems to work. It's a bit more like what you started with, but handles each column individually, then performs tests like I did previously

set up sheet:
A1:A17 formula "= RAND()"
B1:B17 values 1-17 in order
C1:C17 named range "personnelList"
C1 formula "=VLOOKUP(LARGE($A$1:$A$17,B1),$A$1:$B$17,2,FALSE)", copied down to C17
D1:G17 output section, blank at start of VBA run
H1 formula "=COUNTIF(D1:G1,C1)>0", copied down
H18 formula "=COUNTIF(H1:H17,TRUE)=0", named range "noDuplications"

code:

Sub writeGroups17()
Dim i As Integer
For i = 1 To 4
    Do
        Application.Calculate
        If Range("noDuplications").Value = True Then
            Range("personnelList").Copy
            Range("personnelList").Offset(0, i).PasteSpecial (xlPasteValues)
            Exit Do
        End If
    Loop
Next i
End Sub

I won't guarantee this gives results that meet your criteria on every occasion, but it seems to work the first few runs I've done. To guarantee it, you need to adjust the "noDuplications" formula to check there are no duplications within each individual group

 

[PM1]

The closest I have come to getting this to a unique state is I suppose 98% which I can live with, ie only 2 duplicate matches, shown as such

Excel Workbook
	A	B	C	D	E	F	G	H	I
2	Col A	Col B	Col C	Col D
3	1	17	9	4		1		16	A
4	2	6	11	8		1		11	B
5	3	11	16	16		0		5	C
6	4	13	4	9		0		6	D
7	5	1	7	12		1		15	E
8	6	5	13	1		1		3	F
9	7	8	17	2		1		2	G
10	8	12	9	11		1		13	H
11	9	2	2	14		0		8	I
12	10	15	14	7		1		7	J
13	11	9	10	10		0		12	K
14	12	3	1	5		1		17	L
15	13	4	6	17		1		1	M
16	14	14	5	3		0		10	N
17	15	16	3	6		1		14	O
18	16	10	8	13		1		4	P
19	17	7	12	15		1		9	Q
20
21		Day 1	Day 2	Day 3	Day 4
22	Group 1	M	L	Q	P		1
23		G	D	B	I		1
24		F	B	A	A		0
25	Group 2	P	H	P	Q		0
26		C	M	J	K		1
27		D	C	H	M		1
28	Group 3	J	I	L	G		1
29		I	K	Q	B		1
30		Q	G	G	O		0
31	Group 4	N	E	O	J		1
32		B	Q	N	N		0
33		K	F	M	C		1
34		H	P	D	L		1
35	Group 5	O	O	C	F		0
36		E	A	F	D		1
37		A	N	I	H		1
38		L	J	K	E		1
Sheet1

Took me ages to work out as I struggled to automate this Matrix, WXYZ is Day 1,2,3,4

Excel Workbook
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Matrix of Who's Who
2		A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3	A		Y			W				Z			W			W	Z	Y
4	B	Y			X			Z	W			W	X		W	Z		Y
5	C				W		Y		X	Y	Z	Y	Z	X	Z		W
6	D		X	W		Z	Z		Z				X	Y	Y	Y	W
7	E	W			Z		X/Z		Z				W			W	X	X
8	F			Y	Z	X/Z		W	Z	Y		Y		W			X	X
9	G		Z				W			X		X	Y	W		Z		Y
10	H		W	X	Z	Z	Z				Y	W		X	W		Y
11	I	Z		Y			Y	X			W	X/Y					Z	W
12	J			Z					Y	W			Z		Z		Y	W
13	K		W	Y			Y	X	W	X/Y				Z	W			Z
14	L	W	X	Z	X	W		Y			Z				Z	W		Y
15	M			X	Y		W	W	X			Z			Y	Y		Z
16	N		W	Z	Y				W		Z	W	Z	Y		Y
17	O	W	Z		Y	W		Z					W	Y	Y
18	P	Z		W	W	X	X		Y	Z	Y							X
19	Q	Y	Y			X	X	Y		W	W	Z	Y	Z			X
Sheet1

I could get the basic Match right but where it duplicated threw me. Any suggestions?