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 |
|---|
|
|---|
| J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI |
|---|
| 7 | 1 | 1 | 3 | 7 | 9 | | 1 | 3 | 7 | 9 | | | | | | | 0 | 2 | 4 | 5 | 6 | 8 | | | | |
|---|
| 8 | 1 | 2 | 6 | 8 | 10 | | 0 | 1 | 2 | 6 | | | | | | | 3 | 4 | 5 | 7 | 9 | | | | | |
|---|
| 9 | 6 | 6 | 7 | 8 | 9 | | | | | | | | | | | | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
|---|
| 10 | 3 | 4 | 4 | 6 | 9 | | 3 | 4 | 6 | 9 | | | | | | | 0 | 1 | 2 | 5 | 7 | 8 | | | | |
|---|
| 11 | 4 | 6 | 7 | 7 | 8 | | 4 | 6 | 7 | 8 | | | | | | | 0 | 1 | 2 | 3 | 5 | 9 | | | | |
|---|
|
|---|
