Running a Macro Until result Achieved

[Thunderace]

Previously started this at
http://www.mrexcel.com/forum/showthr...24#post2853724 to get maximum response as I've been playing with this problem for more than a month


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?


9 6 16 2 1
15 18 10 5 1
17 17 2 10 0
10 5 3 17 1
14 13 14 18 0
2 7 17 12 1
12 14 11 3 1
7 9 7 4 0
11 2 12 15 1
3 8 4 14 1
18 4 9 6 1
13 3 6 7 1
5 10 5 8 0
6 11 18 16 1
16 15 15 11 0
4 12 13 9 1
8 16 8 13 0
NOT YET

[JBeaucaire]

If the cell to "check" were cell F20, then this macro should work:

Please Login or Register  to view this content.

[Thunderace]

It's on a permanent loop, I'm now wondering if the result will ever be achieved.

Surely there must be a permutation to achieve 4 columns of 17 no's where each row is unique

[JBeaucaire]

CTRL-Break to end the loop. Surely? I don't know about that. Totally dependent on your method.

I would create a list of the numbers to use, then use a RAND() function next to that list to get a random order for selecting those values, then SMALL() to grab the randomized values.

'RANDOMIZE A LIST
I have a macro that may be "ready to use" for randomizing data in column A.

Randomization - Solution 1 - Macro

There's also a solution 2 that shows a formula based technique.

[Thunderace]

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.

[Andrew-R]

Think of it this way - each row contains 10 unique pairs of columns (AB, AC, AD, AE, BC, BD, BE, **, CE, DE) with 16 chances in 17 that the columns in each pair are different. So, the chances of all of the pairs of columns in a row being different is (16/17)^10 = ~55%.

If you have 17 rows then the chance of getting all unique results is (55%)^17.

So based on this back-of-a-fag-packet maths you'd have to loop, on average, 30,000 times to get a qualifying result. Maybe iteration isn't the way to go.

[Thunderace]

So adding 4Gb Ram and Quad Proc is not enough

[JBeaucaire]

The technique I outlined is the one I would employ for your problem, too.

1) Create a main range listing our numbers 1-18. These values could really be anything.
2) In adjacent columns, put in a standard RAND() formula to create random precision decimal values for each value in our main range
3) Use the SMALL function to find the smallest random number in second column to return the index value from the first column. SMALL(Range, {1-5}) would be used to pull each of the 5 smallest random values.

4) Now, since you want 17 groups, I would make 17 separate RAND() columns, each row would use its own RAND() column to create its group of numbers.

Now your list should be SUCCESS 100% of the time.

[JBeaucaire]

The only thing you want o do is make sure that within your 17 groups of random numbers, you don't accidentally randomly create the same number set. I added a DOUBLE-CHECK section here to insure they are all unique. Keep pressing F9 until you see some numbers light up in column L.

[Thunderace]

Got to a point where I've accepted that I have 2 duplicates.

I couldn't get the Match in the Matrix to work because of the duplicate so I sort of gave up and done it manually.

Have attached the file for anybody interested in seeing what i was trying to achieve.

A3:D19 was the Number Randomisation I was trying to achieve

B22:E38 were the groups that I was trying to make unique.

J2:AA19 was the check to show who was paired with who substituting W,X,Y & Z for Day 1,2,3 & 4 in B21:E21

Will leave this open for a few days while I play arounf to see if I can get it all automated.

Thanks for all who have contributed.