looking along a line and telling me what numbers are missing

[tonylpcs@yahoo.co.uk]

Hi everyone,

i have a row of numbers in cells e27 to x27 and they are random numbers between 1 and 20 but each cell has a different number in it.
however sometimes a row will have two numbers that are missing!
how can i get excel to, for instance in cell y27, look along the line and tell me what numbers are missing? theres no problem with it either putting all missing numbers in one cell or filling up different cells or even putting in the missing numbers in the empty cells just need to do it?

any ideas?

tony

 

[markmzz]

Try this array formula (use Ctrl+Shift+Enter and not only Enter):

<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 /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th><th>AF</th><th>AG</th><th>AH</th><th>AI</th><th>AJ</th><th>AK</th><th>AL</th><th>AM</th><th>AN</th><th>AO</th><th>AP</th><th>AQ</th><th>AR</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Helper row</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">5</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style="text-align: center;;">8</td><td style="text-align: center;;">9</td><td style="text-align: center;;">10</td><td style="text-align: center;;">11</td><td style="text-align: center;;">12</td><td style="text-align: center;;">13</td><td style="text-align: center;;">14</td><td style="text-align: center;;">15</td><td style="text-align: center;;">16</td><td style="text-align: center;;">17</td><td style="text-align: center;;">18</td><td style="text-align: center;;">19</td><td style="text-align: center;;">20</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><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><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;">2</td><td style="text-align: right;;"></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><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><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><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><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><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;"></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><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><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><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><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><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">14</td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;">16</td><td style="text-align: center;background-color: #FFFF00;;">20</td><td style="text-align: center;background-color: #FFFF00;;">7</td><td style="text-align: center;background-color: #FFFF00;;">15</td><td style="text-align: center;background-color: #FFFF00;;">17</td><td style="text-align: center;background-color: #FFFF00;;">3</td><td style="text-align: center;background-color: #FFFF00;;">4</td><td style="text-align: center;background-color: #FFFF00;;">10</td><td style="text-align: center;background-color: #FFFF00;;">5</td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;">6</td><td style="text-align: center;background-color: #FFFF00;;">8</td><td style="text-align: center;background-color: #FFFF00;;">13</td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;">18</td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;background-color: #8DB4E3;;">19</td><td style="text-align: center;background-color: #8DB4E3;;">12</td><td style="text-align: center;background-color: #8DB4E3;;">11</td><td style="text-align: center;background-color: #8DB4E3;;">9</td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">18</td><td style="text-align: center;background-color: #FFFF00;;">17</td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;">16</td><td style="text-align: center;background-color: #FFFF00;;">5</td><td style="text-align: center;background-color: #FFFF00;;">7</td><td style="text-align: center;background-color: #FFFF00;;">8</td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;">9</td><td style="text-align: center;background-color: #FFFF00;;">11</td><td style="text-align: center;background-color: #FFFF00;;">20</td><td style="text-align: center;background-color: #FFFF00;;">12</td><td style="text-align: center;background-color: #FFFF00;;">4</td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;">19</td><td style="text-align: center;background-color: #FFFF00;;">3</td><td style="text-align: center;background-color: #FFFF00;;">10</td><td style="text-align: center;background-color: #8DB4E3;;">15</td><td style="text-align: center;background-color: #8DB4E3;;">14</td><td style="text-align: center;background-color: #8DB4E3;;">13</td><td style="text-align: center;background-color: #8DB4E3;;">6</td><td style="text-align: center;background-color: #8DB4E3;;">2</td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td><td style="text-align: center;background-color: #8DB4E3;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;"></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><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><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><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><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><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;"></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><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><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><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><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">Sheet11</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">Y27</th><td style="text-align:left">{=IF(<font color="Blue">SUM(<font color="Red">--ISERROR(<font color="Green">MATCH(<font color="Purple">$E$1:$X$1,$E27:$X27,0</font>)</font>)</font>)>=COLUMNS(<font color="Red">$Y:Y</font>),LARGE(<font color="Red">--ISERROR(<font color="Green">MATCH(<font color="Purple">$E$1:$X$1,$E27:$X27,0</font>)</font>)*(<font color="Green">$E$1:$X$1</font>),COLUMNS(<font color="Green">$Y:Y</font>)</font>),""</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

 

[tonylpcs@yahoo.co.uk]

thank that is excellent!

thanks for your help

tony

 

[markmzz]

thank that is excellent!

thanks for your help

tony

Tony,

Thanks for the feedback.

Markmzz

 

[T. Valko]

Hi everyone,

i have a row of numbers in cells e27 to x27 and they are random numbers between 1 and 20 but each cell has a different number in it.
however sometimes a row will have two numbers that are missing!
how can i get excel to, for instance in cell y27, look along the line and tell me what numbers are missing? theres no problem with it either putting all missing numbers in one cell or filling up different cells or even putting in the missing numbers in the empty cells just need to do it?

any ideas?

tony

Try this...


Create this defined named string:

• Name: Nums
• Refers to: ={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}

Enter this formula in Y27. This will return the count of missing numbers.

=SUMPRODUCT(--(ISNA(MATCH(Nums,E27:X27,0))))

Enter this array formula** in Z27. This will list the missing numbers.

=IF(COLUMNS($Z27:Z27)>$Y27,"",SMALL(IF(ISNA(MATCH(Nums,$E27:$X27,0)),Nums),COLUMNS($Z27:Z27)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across until you get blanks.