# Missing Digits Return.

0

Last edited:

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### serge

##### Well-known Member
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

#### serge

##### Well-known Member
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
5
Views
191
Replies
5
Views
107
Replies
2
Views
149
Replies
19
Views
205
Replies
15
Views
302 Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,163,408
Messages
5,831,441
Members
430,070
Latest member
Renske ### 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