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

 

[baitmaster]

do until range("I19")= 17
    application.calculate
loop

this will keep going forever if you aren't careful...

 

[PM1]

Still running

Surely the randomisation formula cannot be wrong?, it's giving me numbers from 1 - 17 in 4 different columns. Whats the permutation on this?.

Now this is annoying...............

 

[PM1]

ps - how do I stop it ?

 

[baitmaster]

To stop, hold [escape] until code breaks

17 x 17 x 17 x 17 = 83,521 possible scenarios, assumin you only look at an "ordered" list. Randomising things might lead to more or less before a result is found

It might help if you ensure you only generate numbers that have not been used in the same loop, i.e. prevent duplication before you write the values into your table?

 

[baitmaster]

ok, so let me get this straight, you are simply trying to generate 17 sets of unique numbers taken randomly from the set 1-17?

Your problem in what you have done so far is that you are trying to get every combination to be unique at the same time, and if not you are generating another set of data. Do them 1 at a time, then move on when each is ok

 

[PM1]

"baitmaster" you are correct. I know I can do 1 - 17 and step down 1 in each column but to me that is not random.

 

[baitmaster]

so long as you produce a line of random numbers for each set of results, whether you produce them all in one hit, or 1 row at a time, surely each line is still random and hence randomness ensues throughout?

Or am I missing something in the complexity of your formula? Does a certain type of result influence any other line? I don't believe it should, and hence I think my approach remains ok

I believe you can simply create a line of random numbers, where each number is unique, then generate another line of random numbers etc. The only thing I see that prevents true randomness is that the numbers cannot be duplicated

Im off home in a minute, but I have a random number generator there that continually produces sequences of unique random numbers using VBA. I'll post the code later if I find it

 

[PM1]

If only it was that simple. I have arranged each column into 3 groups of 3 and 2 groups of 4.

My idea is to achieve a state where each group is unique in that at no time does any group match any other, I will then achieve 12 groups of 3 and 8 groups of 4 all unique.

it is similar in a way to http://en.wikipedia.org/wiki/Nurse_scheduling_problem in that there is a lot of difficulties encountered in its constraint.

 

[baitmaster]

OK...

here is a piece of code that I just wrote, that will meet the needs I described in my previous post

Sub randomDataNoDuplication()
Dim i As Integer, j As Integer, thisRand As Integer
With Range("randoms")
    For i = 1 To .Rows.Count
        For j = 1 To .Columns.Count
repeatRand:
            thisRand = WorksheetFunction.RoundUp(Rnd() * 17, 0)
            .Cells(i, j) = thisRand
            If WorksheetFunction.CountIf(.Rows(i), thisRand) > 1 Then GoTo repeatRand
        Next j
    Next i
End With
End Sub

To make it work, you need to name your output section "randoms"

It doesn't quite manage what you want given your new criteria, but shows how you could structure some VBA to do this, In effect I'm looping through and generating new randoms, then regenerating them if duplication is found. You could probably add an additional test that regenerates the entire line when needed. I'll think about how to go about this next...