EXCEL help please...?

[sezuh]

Hi,
Thanks for the excellent advise and help you gave me in my previous treads,very much appreciated.
I’m hoping you would spare me some of your time for this one.
I have a list of 2470 rows (col. A ) made of 1 to 40 numbers each cell consist of 4 numbers ,and i have another list in col.”C” made of 1 to247 consecutive numbers,i would like assign those numbers to each row in col.”B”with equal proportion,in such a way each number(1 to 247) to be assign to(1 to 40) in column “A” only once.I hope somehow i’m making some sense....
Here as an example, the only reason i input list in 3 column is to save same space but the logic is the same,i assign 28 numbers to 84 rows equally.
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 33px"><COL style="WIDTH: 64px"><COL style="WIDTH: 45px"><COL style="WIDTH: 64px"><COL style="WIDTH: 39px"><COL style="WIDTH: 134px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>list</TD><TD> </TD><TD>list cont.</TD><TD> </TD><TD>list cont.</TD><TD> </TD><TD>Nmbers to be assign</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">01 02 03</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">02 03 04</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: right">03 05 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">27</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">01 02 04</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">02 03 05</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">03 05 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">4</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">01 02 05</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">02 03 06</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">03 06 07</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">18</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">01 02 06</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">02 03 07</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">03 06 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">5</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">01 02 07</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">02 03 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">03 06 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">6</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">01 02 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">02 03 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: right">03 07 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">7</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">01 02 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">02 04 05</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">03 07 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">3</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">7</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">01 03 04</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">02 04 06</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">27</TD><TD style="TEXT-ALIGN: right">03 08 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">2</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">01 03 05</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">02 04 07</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">28</TD><TD style="TEXT-ALIGN: right">04 05 06</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">7</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">9</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">01 03 06</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">02 04 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">04 05 07</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">6</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">10</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">01 03 07</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">02 04 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">04 05 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">1</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">11</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">01 03 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">02 05 06</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">04 05 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">5</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">12</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">01 03 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">02 05 07</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">04 06 07</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">21</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">13</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">01 04 05</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">02 05 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right">04 06 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">3</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">14</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">01 04 06</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">02 05 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">26</TD><TD style="TEXT-ALIGN: right">04 06 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">20</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">15</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">01 04 07</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">02 06 07</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">04 07 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">4</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">16</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">01 04 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">17</TD><TD style="TEXT-ALIGN: right">02 06 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">22</TD><TD style="TEXT-ALIGN: right">04 07 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">12</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">17</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">01 04 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right">02 06 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">17</TD><TD style="TEXT-ALIGN: right">04 08 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">9</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">18</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">01 05 06</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right">02 07 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right">05 06 07</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">2</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">19</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: right">01 05 07</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">02 07 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">05 06 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">8</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">20</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: right">01 05 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: right">02 08 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">23</TD><TD style="TEXT-ALIGN: right">05 06 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">11</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">21</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: right">01 05 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">22</TD><TD style="TEXT-ALIGN: right">03 04 05</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">23</TD><TD style="TEXT-ALIGN: right">05 07 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">10</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">22</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">01 06 07</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">23</TD><TD style="TEXT-ALIGN: right">03 04 06</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">26</TD><TD style="TEXT-ALIGN: right">05 07 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">24</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">23</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: right">01 06 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: right">03 04 07</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">22</TD><TD style="TEXT-ALIGN: right">05 08 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">15</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">24</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="TEXT-ALIGN: right">01 06 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">03 04 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">06 07 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">13</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">25</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD style="TEXT-ALIGN: right">01 07 08</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">26</TD><TD style="TEXT-ALIGN: right">03 04 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right">06 07 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">1</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">26</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD style="TEXT-ALIGN: right">01 07 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">27</TD><TD style="TEXT-ALIGN: right">03 05 06</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">28</TD><TD style="TEXT-ALIGN: right">06 08 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">16</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">27</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD style="TEXT-ALIGN: right">01 08 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">28</TD><TD style="TEXT-ALIGN: right">03 05 07</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">17</TD><TD style="TEXT-ALIGN: right">07 08 09</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">14</TD><TD style="COLOR: #008080; TEXT-ALIGN: right">28</TD></TR></TBODY></TABLE>

Thanks in advance for your help and suggestions.
Sezuh

 

[shawnhet]

Ok, here goes:

Take the Code from the link I reference above and insert it into the VBE - you'll note the two lines there.

Workbooks.Add

iLimit = 52

Delete Workbooks.Add and change the 52 to 2470. Run the Macro, This will give you a column of unique random numbers btween 1 and 2470. Populate the column next to it with your 4 digit sets(however they are formatted). The column next to that (C) one will be a single unique (decimal) number constructed from your 4 digit set somehow(it is really up to you).

in D1 add this formula

=int((A1-1)/247)+1 and fill down ~ this gives you which set of 10 this row belongs to

in E1 put this formula and fill down.

=((A1-1)/247-int((A1-1)/247))*247+1 ~ this gives you the number btw 1 and 247 the row belongs to.

then you can sort by columns D and E if you prefer.

Now all that is left to do is rule out any duplicates. As I said previously, there are hundreds of millions of combinations of your 4 digit sets and numbers btw 247 so i am not really enthusiastic about putting in a lot of time to find a solution here. On an ad hoc basis you could probably use a Countifs statement for column C with same column E number> 1 statement as a preliminary test.

Hope that helps,

Cheers,

 

[sezuh]

Shawnhet,
Thanks for your help here is the result from your suggestion,if you notice in first 10 number "1" not assigned to number 1 but three times been assigned to number"3"those numbers in column"E" should be assigned to each numbers "1:40" in column"B" .But i think we're doing something to shed light to the problem...
Thanks again for your invaluable advice.
Sheet4

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 70px"><COL style="WIDTH: 91px"><COL style="WIDTH: 79px"><COL style="WIDTH: 33px"><COL style="WIDTH: 54px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">1</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">15 18 22 32</TD><TD style="TEXT-ALIGN: right">2396832</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">1</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">248</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">06 17 24 39</TD><TD style="TEXT-ALIGN: right">1947239</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">2</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">495</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">03 07 14 16</TD><TD style="TEXT-ALIGN: right">1099216</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">3</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">742</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">03 07 23 27</TD><TD style="TEXT-ALIGN: right">1675227</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">4</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">989</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">03 05 28 39</TD><TD style="TEXT-ALIGN: right">1992039</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">5</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">1236</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">08 14 23 39</TD><TD style="TEXT-ALIGN: right">2006439</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">6</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">1483</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">04 17 28 33</TD><TD style="TEXT-ALIGN: right">2075233</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">7</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">1730</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">06 19 28 37</TD><TD style="TEXT-ALIGN: right">2206437</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">8</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">1977</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">13 14 23 25</TD><TD style="TEXT-ALIGN: right">2326425</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">9</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">2224</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">22 29 31 40</TD><TD style="TEXT-ALIGN: right">3438440</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">741</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">03 17 23 37</TD><TD style="TEXT-ALIGN: right">1691237</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">3</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">247</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">988</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">03 09 11 12</TD><TD style="TEXT-ALIGN: right">910412</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">4</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">247</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">247</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">11 18 33 36</TD><TD style="TEXT-ALIGN: right">2844836</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">1</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">247</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">494</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">02 10 25 27</TD><TD style="TEXT-ALIGN: right">1744027</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">2</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">247</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">1235</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">04 06 24 26</TD><TD style="TEXT-ALIGN: right">1801626</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">5</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">247</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">1482</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">01 20 25 36</TD><TD style="TEXT-ALIGN: right">1696036</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">6</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">247</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">1729</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">06 09 24 31</TD><TD style="TEXT-ALIGN: right">1934431</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">7</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">247</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">1976</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">05 06 33 37</TD><TD style="TEXT-ALIGN: right">2441637</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">8</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">247</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">2223</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">01 08 09 10</TD><TD style="TEXT-ALIGN: right">652810</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">9</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">247</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman; TEXT-ALIGN: right">2470</TD><TD style="FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">23 25 34 40</TD><TD style="TEXT-ALIGN: right">3688040</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">247</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D1</TD><TD>=INT((A1-1)/247)+1</TD></TR><TR><TD>E1</TD><TD>=((A1-1)/247-INT((A1-1)/247))*247+1</TD></TR><TR><TD>D2</TD><TD>=INT((A2-1)/247)+1</TD></TR><TR><TD>E2</TD><TD>=((A2-1)/247-INT((A2-1)/247))*247+1</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[shawnhet]

Ok, I see what you're asking for now and why everyone else has dropped out . This is some sort of massive Sudoku type puzzle.

Well, good luck with it, but, frankly, this is way too complicated for me to want to look at when it serves no practical purpose.

Best wishes,

 

[sezuh]

Thanks for your time and effort,much appreciated.
Tha search goes on for the Holy Grail..........

 

[sezuh]

Hi,
Very sorry for bumping.......?

 

[Andrew Fergus]

Quick question. Let's assume for the moment (rightly or wrongly) that the values 39 and 40 are the only possible values that could be assigned to 3 unique rows (for the purposes of this example there are no other possible values that could go into these 3 rows - they could be say rows 300:302). Under this scenario I presume the problem can not be solved given 2 cannot be divided into 3 - is that correct?

 

[sezuh]

Hi,
Thanks for your time and being still interested in this problem.

Quick question. Let's assume for the moment (rightly or wrongly) that the values 39 and 40 are the only possible values that could be assigned to 3 unique rows (for the purposes of this example there are no other possible values that could go into these 3 rows - they could be say rows 300:302). Under this scenario I presume the problem can not be solved given 2 cannot be divided into 3 - is that correct?

to be honest i dont understand and i can not say if correct or not?Because it depend what numbers in those rows and what numbers has been allocated and also we have to look to the whole picture and find a way or excel formula to distribute those "1;247" to the "1:40" in 2470 rows combinations.
I dont think i can explain more clearer than that..

 

[Andrew Fergus]

If we can't get a clear understanding of eachother then that is going to be a problem, so I will try again. My example is no different to a SuDoKu example.

Let's assume we are looking at the puzzle as a whole, and I can see that rows 300, 301 and 302 can only contain the values 39 and 40 given various constraints (I know for practical purposes this is unlikely, but let's assume this is the case for this example). For the purposes of this example, those 3 rows cannot contain any other possible values (e.g. I can't allocate the values 1-38 or 41-247 for whatever reason to these 3 rows). Given I cannot allocate 39 (or 40) more than once, then under this scenario is it correct to say this puzzle can not be solved?

Andrew