finding gaps in numbers

[thewho]

Hello,

I am trying to find some gaps in a set of numbers. I did find this video (https://www.youtube.com/watch?v=_jel_Otcgds) however it did not work for my set of numbers. possibly due to how the numbers are formatted. These numbers are formatted using a custom format field. I have listed below some sample numbers. I am trying to find out the following
A) find out if there is a gap in the numbers
B) use a formula to fill in that gap

I don't think that this all can be done in one formula however it is past my level of understanding.

3-33-1309-0-0
3-33-1314-0-0
3-33-1311-0-0
3-33-1316-0-0
3-33-1313-0-0
3-33-1320-0-0
3-33-1322-0-0
3-33-1324-0-0
3-33-1330-0-0
3-33-1332-0-0
3-27-9709-0-0
3-34-1390-0-0
3-34-1306-0-0
3-34-1308-0-0
3-34-1312-0-0

<colgroup width="117"></colgroup> <tbody>
</tbody>

<style type="text/css"> body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Arial"; font-size:x-small } a.comment-indicator:hover + comment { background:#ffd; position:absolute; display:block; border:1px solid black; padding:0.5em; } a.comment-indicator { background:red; display:inline-block; border:1px solid black; width:0.5em; height:0.5em; } comment { display:none; } </style>

 

[Rick Rothstein]

Hello,

I am trying to find some gaps in a set of numbers. I did find this video (https://www.youtube.com/watch?v=_jel_Otcgds) however it did not work for my set of numbers. possibly due to how the numbers are formatted. These numbers are formatted using a custom format field. I have listed below some sample numbers. I am trying to find out the following
A) find out if there is a gap in the numbers
B) use a formula to fill in that gap

I don't think that this all can be done in one formula however it is past my level of understanding.

3-33-1309-0-0
3-33-1314-0-0
3-33-1311-0-0
3-33-1316-0-0
3-33-1313-0-0
3-33-1320-0-0
3-33-1322-0-0
3-33-1324-0-0
3-33-1330-0-0
3-33-1332-0-0
3-27-9709-0-0
3-34-1390-0-0
3-34-1306-0-0
3-34-1308-0-0
3-34-1312-0-0

<colgroup width="117"></colgroup> <tbody>
</tbody>

<style type="text/css"> body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Arial"; font-size:x-small } a.comment-indicator:hover + comment { background:#ffd; position:absolute; display:block; border:1px solid black; padding:0.5em; } a.comment-indicator { background:red; display:inline-block; border:1px solid black; width:0.5em; height:0.5em; } comment { display:none; } </style>

If the cells are custom formatted, then show us an actual value shown in the Formula Bar and the value for it in the cell. Also showing us the custom format type pattern would be helpful. Also, you need to show us what number need to go between to fill the "gap" (choose numbers for your example that make the "gap" small so that the list of gap fillers is not too large).

 

[thewho]

The numbers as they are in the formula bar are "333130900". I have also attached the screen shot of the formatting that is currently in use.
https://imgur.com/a/bNo38y1

 

[Rick Rothstein]

The numbers as they are in the formula bar are "333130900". I have also attached the screen shot of the formatting that is currently in use.
https://imgur.com/a/bNo38y1

Why is the font size on your posts so small... you messages are very hard to read because of it.

You did not answer my other question... what numbers are you expecting to fill the gap? Is the last two digits always 0? Is the gap calculated from the values in the 4-digit group? Do we have to pay attention to the first three digits when working out the gap?

 

[thewho]

Hello,

I should have been more clear sorry about that got a bit frustrated yesterday. I am trying gaps for instance in 3-33-1309-0-0 thru 3-33-1332-0-0 are open. So I would like to find a gap in those numbers and then fill those number in that are missing. any other numbers we are not worried about at this time.

 

[thewho]

Is it possible to use an IF statement for a defined set of numbers like what I would like to do?