# Missing Digits Return.

0

Last edited:

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Disregard the first post, Thank you because I had tho shrink the excel file post.

Hi,

I'm having a bit of a problem with those formulas I can't make 2 posts for it because they work together and it would not be easy for me to explain.

For example In J:N I have 5 digit and sometime duplicate from 1:9. ( No "0")
In P:Y those formulas they trim J:N and return the digits used without duplicate.
In Z:AI that formula return the digits that are missing from P:Y and it work fine.

Now I added the number "10" in the columns J:N so now the it goes from 1:10 and not 1:9.
The problem I get is that the formulas in P:Y are returning a "0" instead of a 10 and the same in Z:AI ???
what need to be change for the formulas to return a 10 ?

Formula in P :

=IF(ISERROR(FIND(0,\$J7&\$K7&\$L7&\$M7&\$N7,1)),IF(ISERROR(FIND(1,\$J7&\$K7&\$L7&\$M7&\$N7,1)),IF(ISERROR(FIND(2,\$J7&\$K7&\$L7&\$M7&\$N7,1)),IF(ISERROR(FIND(3,\$J7&\$K7&\$L7&\$M7&\$N7,1)),IF(ISERROR(FIND(4,\$J7&\$K7&\$L7&\$M7&\$N7,1)),IF(ISERROR(FIND(5,\$J7&\$K7&\$L7&\$M7&\$N7,1)),"",5),4),3),2),1),0)

Formula in Q :

=IF(P7="","",IF(P7+1>9,"",IF(ISERROR(FIND(P7+1,\$J7&\$K7&\$L7&\$M7&\$N7,1)),IF(P7+2>9,"",IF(ISERROR(FIND(P7+2,\$J7&\$K7&\$L7&\$M7&\$N7,1)),IF(P7+3>9,"",IF(ISERROR(FIND(P7+3,\$J7&\$K7&\$L7&\$M7&\$N7,1)),IF(P7+4>9,"",IF(ISERROR(FIND(P7+4,\$J7&\$K7&\$L7&\$M7&\$N7,1)),IF(P7+5>9,"",IF(ISERROR(FIND(P7+5,\$J7&\$K7&\$L7&\$M7&\$N7,1)),IF(P7+6>9,"",IF(ISERROR(FIND(P7+6,\$J7&\$K7&\$L7&\$M7&\$N7,1)),"",P7+6)),P7+5)),P7+4)),P7+3)),P7+2)),P7+1)))

Formula in Z :

=MATCH(0, FREQUENCY(\$P7:Y7, {0,1,2,3,4,5,6,7,8}), 0) - 1

Also when the Digits start hight like in row 9 it start at 6 6 7 8 9 the formula don't return nothing ?

Thank you.
Excel Workbook
7113791379024568
8126810012634579
9667890123456789
10344693469012578
11467784678012359
Sheet

Hello, does anyone have an idea how to fix it ? I really need it to continue my work.

Disregard the 1st post start at the 2nd one.

Thank you.

Last edited:

Replies
2
Views
139
Replies
12
Views
213
Replies
9
Views
271
Replies
4
Views
128
Replies
13
Views
489

1,216,172
Messages
6,129,291
Members
449,498
Latest member
Lee_ray

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back