Need 2 formulas to count skipping number.

[serge]

I need 2 formulas please.

First I have a list of 7050 numbers of 3 digits each.
And I would like to be able to see when a number has been draw again ?

The first formula starting in column B5 will count how many draws ( or rows ) pass by before that same number appear again.

The second formula in column starting in C5 will do the same thing, but the formula would be able to recognize the number in it's 5 differentes positions.

Example :

Number 149 = 5 diferente positions : 194-419-491-914-941.

I don't know if it's possible !!? but if it is that would be helpful to me.

Question :

Should I have on a different sheet the list of all the numbers which would start at 000:999 ?


<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="background-color:#00ff00; text-align:center; ">149</td><td>
</td><td style="text-align:center; ">12</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:center; ">809</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:center; ">074</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="text-align:center; ">067</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="text-align:center; ">091</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style="text-align:center; ">788</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td style="background-color:#00ff00; text-align:center; ">149</td><td style="text-align:center; ">6</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td style="text-align:center; ">008</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td style="text-align:center; ">960</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td style="background-color:#00ff00; text-align:center; ">149</td><td style="text-align:center; ">3</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td style="text-align:center; ">900</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">16</td><td style="background-color:#ff0000; text-align:center; ">194</td><td>
</td><td style="text-align:center; ">11</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">17</td><td style="text-align:center; ">257</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">18</td><td style="text-align:center; ">798</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">19</td><td style="text-align:center; ">247</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">20</td><td style="text-align:center; ">628</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">21</td><td style="text-align:center; ">430</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">22</td><td style="background-color:#ff0000; text-align:center; ">419</td><td>
</td><td style="text-align:center; ">6</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">23</td><td style="text-align:center; ">859</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">24</td><td style="text-align:center; ">224</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">25</td><td style="text-align:center; ">052</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">26</td><td style="background-color:#ff0000; text-align:center; ">941</td><td>
</td><td style="text-align:center; ">4</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">27</td><td style="text-align:center; ">803</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">28</td><td style="text-align:center; ">525</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">29</td><td style="text-align:center; ">582</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">30</td><td style="text-align:center; ">457</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">31</td><td style="text-align:center; ">096</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">32</td><td style="background-color:#00ff00; text-align:center; ">149</td><td style="text-align:center; ">18</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">33</td><td style="text-align:center; ">914</td><td>
</td><td style="text-align:center; ">7</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">34</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you for any help.

Serge.

 

[Marcelo Branco]

-Correction-

Array-Formula in C5
=IF(A5<>$D$2,IF(SUM(--ISNUMBER(SEARCH(MID(TEXT(A5,"000"),{1,2,3},1),$D$2)))=3,ROW()-ROW($A$5)-SUM($C$4:C4),""),"")

Ctrl+Shift+Enter

M.

 

[markmzz]

Serge,

Try the formulas below and tell us if work.

Note: without array formula.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;background-color: #D8D8D8;;">149</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;background-color: #00FF00;;">149</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;background-color: #D8D8D8;;">1</td><td style="font-weight: bold;text-align: center;background-color: #D8D8D8;;">4</td><td style="font-weight: bold;text-align: center;background-color: #D8D8D8;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">809</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;background-color: #FFFF00;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">074</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;background-color: #FFFF00;;">3</td><td style="text-align: center;background-color: #FFFF00;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">067</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;background-color: #FFFF00;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">091</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;background-color: #FFFF00;;">3</td><td style="text-align: center;background-color: #FFFF00;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">788</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">3</td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;background-color: #FFFF00;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;background-color: #00FF00;;">149</td><td style="text-align: center;;">6</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">3</td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;background-color: #FFFF00;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">008</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">960</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;background-color: #00FF00;;">149</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">900</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;background-color: #FF0000;;">194</td><td style="text-align: center;;"></td><td style="text-align: center;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">257</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">798</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">247</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">628</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">430</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;background-color: #FF0000;;">419</td><td style="text-align: center;;"></td><td style="text-align: center;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">859</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;">224</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">052</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;background-color: #FF0000;;">941</td><td style="text-align: center;;"></td><td style="text-align: center;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;;">803</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;;">525</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;;">582</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;;">457</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;;">096</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;background-color: #00FF00;;">149</td><td style="text-align: center;;">18</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: center;background-color: #FF0000;;">914</td><td style="text-align: center;;"></td><td style="text-align: center;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet33</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=IF(<font color="Blue">A5=A$3,COUNTIF(<font color="Red">A$5:A5,"<>"&A5&"*"</font>)+COUNTIF(<font color="Red">A$4:A4,A5</font>)-SUM(<font color="Red">B$4:B4</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=IF(<font color="Blue">A5<>A$3,IF(<font color="Red">(<font color="Green">SUM(<font color="Purple">--(<font color="Teal">COUNTIF(<font color="#FF00FF">A$5:A5,MID(<font color="Navy">A$3,{1,2,2,3,3},1</font>)&MID(<font color="Navy">A$3,{3,1,3,1,2},1</font>)&MID(<font color="Navy">A$3,{2,3,1,2,1},1</font>)</font>)>0</font>)</font>)>COUNT(<font color="Purple">C$4:C4</font>)</font>),COUNTIF(<font color="Green">A$5:A5,"<>"&A5&"*"</font>)-SUM(<font color="Green">C$4:C4</font>),""</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[serge]

Hi Marcelo,

Very nice work ! your formulas works fine, but for some reason the second one : =IF(A5<>$D$2,IF(SUM(--ISNUMBER(SEARCH(MID(TEXT(A5,"000"),{1,2,3},1),$D$2)))=3,ROW()-ROW($A$5)-SUM($C$4:C4),""),"")

doesn't return the right answer, for some numbers after I tested it, but the first one do. so I will keep the first one.

I wanted to ask, and that is the purpose of this chart I'm doing, is to have in each cell column B and C, THE SKIP FOR EVERY NUMBER.

So, I know that the formula need a reference to work, how can this be done with your formulas ?

Number 149 was just for the example for you guys to understand what the formula need to do.

What do you suggest, for this to work ?

Thank you very much for what you have done already.

Serge.

 

[serge]

Hi Mark,

Thanks for the reply, this new formula in B5 works but give me in return 1 over !!!

In B5 I get 1
In B11 I get 7
In B14 I get 4
In B32 I get 19

Thanks for the work, what I need is your formulas to work for every single number like I just explain to Marcelo.

Can this be done ? if yes, How ?

Thank you.

 

[markmzz]

Hi Mark,

Thanks for the reply, this new formula in B5 works but give me in return 1 over !!!

In B5 I get 1
In B11 I get 7
In B14 I get 4
In B32 I get 19

Thanks for the work, what I need is your formulas to work for every single number like I just explain to Marcelo.

Can this be done ? if yes, How ?

Thank you.

Serge,

I will do more tests with my example workbook, do the same.

Markmzz

 

[markmzz]

Serge,

Here are the last formulas:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">195</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">610</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">074</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">067</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">091</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">195</td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">908</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">190</td><td style="text-align: center;;">0</td><td style="text-align: center;;">3</td><td style="text-align: center;;">0</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">915</td><td style="text-align: center;;">0</td><td style="text-align: center;;">3</td><td style="text-align: center;;">0</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">610</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">190</td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">740</td><td style="text-align: center;;">0</td><td style="text-align: center;;">9</td><td style="text-align: center;;">0</td><td style="text-align: center;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">413</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">089</td><td style="text-align: center;;">0</td><td style="text-align: center;;">7</td><td style="text-align: center;;">0</td><td style="text-align: center;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">670</td><td style="text-align: center;;">0</td><td style="text-align: center;;">11</td><td style="text-align: center;;">0</td><td style="text-align: center;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">740</td><td style="text-align: center;;">4</td><td style="text-align: center;;">4</td><td style="text-align: center;;">4</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">879</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">091</td><td style="text-align: center;;">13</td><td style="text-align: center;;">7</td><td style="text-align: center;;">13</td><td style="text-align: center;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">568</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;">559</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">591</td><td style="text-align: center;;">0</td><td style="text-align: center;;">12</td><td style="text-align: center;;">0</td><td style="text-align: center;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;;">470</td><td style="text-align: center;;">0</td><td style="text-align: center;;">6</td><td style="text-align: center;;">0</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;;">610</td><td style="text-align: center;;">13</td><td style="text-align: center;;">13</td><td style="text-align: center;;">13</td><td style="text-align: center;;">13</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;;">067</td><td style="text-align: center;;">20</td><td style="text-align: center;;">9</td><td style="text-align: center;;">20</td><td style="text-align: center;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;;">195</td><td style="text-align: center;;">19</td><td style="text-align: center;;">4</td><td style="text-align: center;;">19</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;;">074</td><td style="text-align: center;;">23</td><td style="text-align: center;;">4</td><td style="text-align: center;;">23</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td></tr></tbody></table><p style="width:10.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Original File-Test</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L5</th><td style="text-align:left">{=IF(<font color="Blue">(<font color="Red">COUNTIF(<font color="Green">I$5:I$30,I5</font>)>1</font>)*(<font color="Red">COUNTIF(<font color="Green">I$5:I5,I5</font>)>1</font>),ROW(<font color="Red"></font>)-MAX(<font color="Red">(<font color="Green">I$4:I4=I5</font>)*ROW(<font color="Green">I$4:I4</font>)</font>),0</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M5</th><td style="text-align:left">{=IF(<font color="Blue">(<font color="Red">SUMPRODUCT(<font color="Green">--ISNUMBER(<font color="Purple">FIND(<font color="Teal">MID(<font color="#FF00FF">TEXT(<font color="Navy">I5,"000"</font>),{1,1,2,2,3,3},1</font>)&MID(<font color="#FF00FF">TEXT(<font color="Navy">I5,"000"</font>),{2,3,1,3,1,2},1</font>)&MID(<font color="#FF00FF">TEXT(<font color="Navy">I5,"000"</font>),{3,2,3,1,2,1},1</font>),TEXT(<font color="#FF00FF">I$5:I$30,"000"</font>)</font>)</font>)</font>)>1</font>)*(<font color="Red">(<font color="Green">SUMPRODUCT(<font color="Purple">--ISNUMBER(<font color="Teal">FIND(<font color="#FF00FF">MID(<font color="Navy">TEXT(<font color="Blue">I5,"000"</font>),{1,1,2,2,3,3},1</font>)&MID(<font color="Navy">TEXT(<font color="Blue">I5,"000"</font>),{2,3,1,3,1,2},1</font>)&MID(<font color="Navy">TEXT(<font color="Blue">I5,"000"</font>),{3,2,3,1,2,1},1</font>),TEXT(<font color="Navy">I$5:I5,"000"</font>)</font>)</font>)</font>)/(<font color="Purple">LOOKUP(<font color="Teal">SUM(<font color="#FF00FF">FIND(<font color="Navy">MID(<font color="Blue">TEXT(<font color="Red">I5,"000"</font>),{1;2;3},1</font>),TEXT(<font color="Blue">I5,"000"</font>)</font>)</font>),{3,6;4,2;6,1}</font>)</font>)</font>)>1</font>),ROW(<font color="Red"></font>)-MAX(<font color="Red">(<font color="Green">--ISNUMBER(<font color="Purple">FIND(<font color="Teal">MID(<font color="#FF00FF">TEXT(<font color="Navy">I5,"000"</font>),{1,1,2,2,3,3},1</font>)&MID(<font color="#FF00FF">TEXT(<font color="Navy">I5,"000"</font>),{2,3,1,3,1,2},1</font>)&MID(<font color="#FF00FF">TEXT(<font color="Navy">I5,"000"</font>),{3,2,3,1,2,1},1</font>),TEXT(<font color="#FF00FF">I$4:I4,"000"</font>)</font>)</font>)</font>)*ROW(<font color="Green">I$4:I4</font>)</font>),0</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[serge]

Hi Mark,

That's a very impressive formula, I tried it and it works fine, I want to thank you a 1000 times for this incredible work.

Best Regard.

Serge.

 

[markmzz]

Serge,

Here is a new formula:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">195</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">610</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">074</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">067</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">091</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">195</td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">908</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">190</td><td style="text-align: center;;">0</td><td style="text-align: center;;">3</td><td style="text-align: right;;">0</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">915</td><td style="text-align: center;;">0</td><td style="text-align: center;;">3</td><td style="text-align: right;;">0</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">610</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td><td style="text-align: right;;">8</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">190</td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">740</td><td style="text-align: center;;">0</td><td style="text-align: center;;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">413</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">089</td><td style="text-align: center;;">0</td><td style="text-align: center;;">7</td><td style="text-align: right;;">0</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">670</td><td style="text-align: center;;">0</td><td style="text-align: center;;">11</td><td style="text-align: right;;">0</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">740</td><td style="text-align: center;;">4</td><td style="text-align: center;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">879</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">091</td><td style="text-align: center;;">13</td><td style="text-align: center;;">7</td><td style="text-align: right;;">13</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">568</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;">559</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">591</td><td style="text-align: center;;">0</td><td style="text-align: center;;">12</td><td style="text-align: right;;">0</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;;">470</td><td style="text-align: center;;">0</td><td style="text-align: center;;">6</td><td style="text-align: right;;">0</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;;">610</td><td style="text-align: center;;">13</td><td style="text-align: center;;">13</td><td style="text-align: right;;">13</td><td style="text-align: right;;">13</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;;">067</td><td style="text-align: center;;">20</td><td style="text-align: center;;">9</td><td style="text-align: right;;">20</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;;">195</td><td style="text-align: center;;">19</td><td style="text-align: center;;">4</td><td style="text-align: right;;">19</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;;">074</td><td style="text-align: center;;">23</td><td style="text-align: center;;">4</td><td style="text-align: right;;">23</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style=";">******</td><td style=";">******</td><td style=";">******</td><td style=";">******</td><td style=";">******</td></tr></tbody></table><p style="width:10.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Original File-Test</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L5</th><td style="text-align:left">{=IF(<font color="Blue">(<font color="Red">COUNTIF(<font color="Green">I$5:I$30,I5</font>)>1</font>)*(<font color="Red">COUNTIF(<font color="Green">I$5:I5,I5</font>)>1</font>),ROW(<font color="Red"></font>)-MAX(<font color="Red">(<font color="Green">I$4:I4=I5</font>)*ROW(<font color="Green">I$4:I4</font>)</font>),0</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M5</th><td style="text-align:left">{=IF(<font color="Blue">(<font color="Red">SUMPRODUCT(<font color="Green">--ISNUMBER(<font color="Purple">MATCH(<font color="Teal">TEXT(<font color="#FF00FF">TEXT(<font color="Navy">I$5:I$30,"000"</font>),"000"</font>),MID(<font color="#FF00FF">TEXT(<font color="Navy">I5,"000"</font>),{1,1,2,2,3,3},1</font>)&MID(<font color="#FF00FF">TEXT(<font color="Navy">I5,"000"</font>),{2,3,1,3,1,2},1</font>)&MID(<font color="#FF00FF">TEXT(<font color="Navy">I5,"000"</font>),{3,2,3,1,2,1},1</font>),0</font>)</font>)</font>)>1</font>)*(<font color="Red">(<font color="Green">SUMPRODUCT(<font color="Purple">--ISNUMBER(<font color="Teal">MATCH(<font color="#FF00FF">TEXT(<font color="Navy">I$5:I5,"000"</font>),MID(<font color="Navy">TEXT(<font color="Blue">I5,"000"</font>),{1,1,2,2,3,3},1</font>)&MID(<font color="Navy">TEXT(<font color="Blue">I5,"000"</font>),{2,3,1,3,1,2},1</font>)&MID(<font color="Navy">TEXT(<font color="Blue">I5,"000"</font>),{3,2,3,1,2,1},1</font>),0</font>)</font>)</font>)</font>)>1</font>),ROW(<font color="Red"></font>)-MAX(<font color="Red">(<font color="Green">--ISNUMBER(<font color="Purple">MATCH(<font color="Teal">TEXT(<font color="#FF00FF">I$4:I4,"000"</font>),MID(<font color="#FF00FF">TEXT(<font color="Navy">I5,"000"</font>),{1,1,2,2,3,3},1</font>)&MID(<font color="#FF00FF">TEXT(<font color="Navy">I5,"000"</font>),{2,3,1,3,1,2},1</font>)&MID(<font color="#FF00FF">TEXT(<font color="Navy">I5,"000"</font>),{3,2,3,1,2,1},1</font>),0</font>)</font>)</font>)*ROW(<font color="Green">I$4:I4</font>)</font>),0</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[markmzz]

Serge,

The last formulas:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">195</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">610</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">074</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">067</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">091</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">195</td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">908</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">190</td><td style="text-align: center;;">0</td><td style="text-align: center;;">3</td><td style="text-align: center;;">0</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">915</td><td style="text-align: center;;">0</td><td style="text-align: center;;">3</td><td style="text-align: center;;">0</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">610</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">190</td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">740</td><td style="text-align: center;;">0</td><td style="text-align: center;;">9</td><td style="text-align: center;;">0</td><td style="text-align: center;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">413</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">089</td><td style="text-align: center;;">0</td><td style="text-align: center;;">7</td><td style="text-align: center;;">0</td><td style="text-align: center;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">670</td><td style="text-align: center;;">0</td><td style="text-align: center;;">11</td><td style="text-align: center;;">0</td><td style="text-align: center;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">740</td><td style="text-align: center;;">4</td><td style="text-align: center;;">4</td><td style="text-align: center;;">4</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">879</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">091</td><td style="text-align: center;;">13</td><td style="text-align: center;;">7</td><td style="text-align: center;;">13</td><td style="text-align: center;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">568</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;">559</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">591</td><td style="text-align: center;;">0</td><td style="text-align: center;;">12</td><td style="text-align: center;;">0</td><td style="text-align: center;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;;">470</td><td style="text-align: center;;">0</td><td style="text-align: center;;">6</td><td style="text-align: center;;">0</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;;">610</td><td style="text-align: center;;">13</td><td style="text-align: center;;">13</td><td style="text-align: center;;">13</td><td style="text-align: center;;">13</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;;">067</td><td style="text-align: center;;">20</td><td style="text-align: center;;">9</td><td style="text-align: center;;">20</td><td style="text-align: center;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;;">195</td><td style="text-align: center;;">19</td><td style="text-align: center;;">4</td><td style="text-align: center;;">19</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;;">074</td><td style="text-align: center;;">23</td><td style="text-align: center;;">4</td><td style="text-align: center;;">23</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td></tr></tbody></table><p style="width:10.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Original File-Test</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L5</th><td style="text-align:left">{=IF(<font color="Blue">COUNTIF(<font color="Red">I$5:I5,I5</font>)>1,ROW(<font color="Red"></font>)-MAX(<font color="Red">(<font color="Green">I$4:I4=I5</font>)*ROW(<font color="Green">I$4:I4</font>)</font>),0</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M5</th><td style="text-align:left">{=IF(<font color="Blue">SUMPRODUCT(<font color="Red">--ISNUMBER(<font color="Green">MATCH(<font color="Purple">TEXT(<font color="Teal">I$5:I5,"000"</font>),MID(<font color="Teal">TEXT(<font color="#FF00FF">I5,"000"</font>),{1,1,2,2,3,3},1</font>)&MID(<font color="Teal">TEXT(<font color="#FF00FF">I5,"000"</font>),{2,3,1,3,1,2},1</font>)&MID(<font color="Teal">TEXT(<font color="#FF00FF">I5,"000"</font>),{3,2,3,1,2,1},1</font>),0</font>)</font>)</font>)>1,ROW(<font color="Red"></font>)-MAX(<font color="Red">(<font color="Green">--ISNUMBER(<font color="Purple">MATCH(<font color="Teal">TEXT(<font color="#FF00FF">I$4:I4,"000"</font>),MID(<font color="#FF00FF">TEXT(<font color="Navy">I5,"000"</font>),{1,1,2,2,3,3},1</font>)&MID(<font color="#FF00FF">TEXT(<font color="Navy">I5,"000"</font>),{2,3,1,3,1,2},1</font>)&MID(<font color="#FF00FF">TEXT(<font color="Navy">I5,"000"</font>),{3,2,3,1,2,1},1</font>),0</font>)</font>)</font>)*ROW(<font color="Green">I$4:I4</font>)</font>),0</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz