Excel sequential numbers with analysis with each sequential change

[DocWEW]

This exercise might teach the students many interactions....

I wish to sequentially change 1,2,3,4,5,6 by one value at a time, up to a total of 39 numbers, 39!/(33!)(6!) = 3,262,623. With each sequence the current value in the 1,2,3,4,5,6 string will be compared against other cells. If a "true" is found, then other data line will be save ... then continue to the next sequence.

eg: 1,2,3,4,5,6 ...... 1,2,3,4,5,39, then 1,2,3,4,6,7, ..... 34,35,36,37,38,39.

As an example:
<TABLE style="WIDTH: 651pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=882 x:str><COLGROUP><COL style="WIDTH: 31pt; mso-width-source: userset; mso-width-alt: 1479" span=21 width=42><TBODY><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 31pt; HEIGHT: 15.6pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl41 height=21 width=42 x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 31pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl42 width=42 x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 31pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl42 width=42 x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 31pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl42 width=42 x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 31pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl42 width=42 x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 31pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl43 width=42 x:num>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl31 width=42></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl38 width=42>#3's</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl39 width=42>#4's</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl40 width=42>#5's</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl25 width=42></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl45 width=42 x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl44 width=42 x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl44 width=42 x:num>18</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl44 width=42 x:num>27</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl44 width=42 x:num>33</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl44 width=42 x:num>36</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=42> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=42 x:num>27</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=42 x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl28 width=42 x:num>1</TD></TR><TR style="HEIGHT: 16.2pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 16.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 height=22 x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>18</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>30</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>33</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl28 x:num>37</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl37></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34 x:num>33</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl35 x:num>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl36 x:num>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl25></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33 x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 x:num>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 x:num>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 x:num>18</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 x:num>24</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 x:num>33</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 x:num>66</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl30 x:num>1</TD></TR></TBODY></TABLE>

If, the sequence arrives to the value(s) of 1,7,18,30,33,37, and in the 33,0,0 cells I wish to make a >= choice of these values.... say if the 33 cell is >=33, and the next is >=0, and the next is also >=0, then copy and save to the area to the next available row at the bottom of the cells to the right of the 33,0,0 cells..... Then proceed to the next number in sequence, 1,7,18,30,33,38.

Thanks, Docwew

 

[shg]

All is does is generate the combinations, one each time it's run.

 

[DocWEW]

Ok, I'll study it.

This is all quite new to me, but I will put in the old "College Try". At this point I do not know how to stop, analyze, copy good numbers, and then continue.

Did you understand my attemps at explaining the rest of the exercise?

Thank you, very much.

 

[shg]

Did you understand the rest of the exercise?

Still nary a clue.

Is it possible within this code to define the user defined "presets"?

You said,

In the background there is an algorithm and its function is to take what appears in A3:F3 and return numbers for H3:J3.

That doesn't strike me as a clear specification.

 

[DocWEW]

I'm so sorry things are not clear. I've tried my very best to make it understandable.

Really the algorithm running in the background no bearing at all on this exercise. I tried to explain it in posts #16 & 17.

With the first sequence of 6 numbers, these 6 numbers returns numbers with show up in H3:J3, then the numbers in H3:J3 are compared to the preset numbers C3:D3, and then the line A3:J3 is either saved or not. After the save decision has been made, the sequencing begins again until the end is reached ... 34,35,36,37,38,39.

Sorry I cannot make it clearer. Thanks for your efforts.

 

[shg]

See the workbook at http://www.box.net/shared/8a9s8r1qb1

 

[DocWEW]

I have it. I'll study it. Thank you, very much.

 

[DocWEW]

I have looked at the code, and I do not see where/what the threshold values (5,3,1) are being compared to in order to return true/false.

There appears to be comparisons being made, and met, because the saved results are selective, but I just don't see where it is happening in the code.

Thanks.

 

[DocWEW]

I see your D3:F3 (5,3,1) are comparable to C9:E9 in post 14 ....

Are there values, somewhere in your code, where your D3:F3 presets are being compared (true/false) before the line is saved?

I.e., analygous to H3:J3 (algorithm inputs) in post 14....

I've looked at your code, and I may be missing something, but I cannot find where, as in post 14, where H3:J3 (as per my algorithm), are being compared to the thresholds C9:E9 (your D3:F3).

 

[shg]

The formula in cell G2, which is named ptrTest

 

[DocWEW]

My formula bar had been turned off somehow.... I have it back. I just did not notice!!

I'll play around with the settings, and then get back with you.

I appreciate your efforts. Thank you.