After setting the formula according to the above figure, the rest of the work is done by VBA, so that the worksheet can be calculated continuously, as long as the cell F6? = 1, F7=0, find the qualified grouping, and then output the result (below 19 lines). Where 1 group, 2 groups and 3 groups have enough people, and two of them are 6, and then the group order can be adjusted manually.
Sub？ sss()
Answer? =? 19
For what? Me? =? 1? Where to? 10000?
z 1？ =? "1 group:":? z2？ =? "the second group:":? z3？ =? "The third group:"
What if? Cell (6, 6)? & lt& gt？ 1? Or? Cells (7, 6)? & lt& gt？ 0? then what
ActiveSheet。 calculate
GoTo？ 10
End? if
For what? j？ =? 2? Where to? 17
Choice? Case? Cell (j, 3)
Case? 1
z 1？ =? z 1？ & amp？ Cell (j, 1)? & amp？ ","
Case? 2
z2？ =? z2？ & amp？ Cell (j, 1)? & amp？ ","
Case? other
z3？ =? z3？ & amp？ Cell (j, 1)? & amp？ ","
End? choose
then
Cell (one, 1)? =? Left (z 1,? Len(z 1)？ -? 1)? & amp？ ";" ? & amp？ Left (z2, Len(z2)? -? 1)? & amp？ ";" ? & amp？ Left (z3, Len(z3)? -? 1)
Answer? =? Answer? +? 1
10
then
End? submarine
The upper limit of the upper loop is to set the number of recalculations. Because it is a random function, there is no guarantee to find the result. The greater the upper limit of the loop, the greater the probability of finding the result, but the longer the running time. This code is still rough. Iteration 10000 times can sometimes find 5 or 6 results, and sometimes only 1 results can be found.