Missing Digits Return.

serge

Well-known Member
Joined
Oct 8, 2008
Messages
1,266
Office Version
  1. 2007
Platform
  1. Windows
0
 
Last edited:

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

serge

Well-known Member
Joined
Oct 8, 2008
Messages
1,266
Office Version
  1. 2007
Platform
  1. Windows
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
JKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
7113791379024568
8126810012634579
9667890123456789
10344693469012578
11467784678012359
Sheet
 

serge

Well-known Member
Joined
Oct 8, 2008
Messages
1,266
Office Version
  1. 2007
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,123,139
Messages
5,599,951
Members
414,351
Latest member
james27

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top