# Identifying the missing gaps between number sequences

#### TheFastGuy

##### New Member
I have a sequence of extension numbers and I would like to find out the appropriate formula to identify the missing sequence. For instance, I have the following extension numbers sorted in ascending order, From this list, I can get the following extension numbers missing in sequence: 1001, 1002, 1003.....1006, 1008, 1009, 1018 and so on. Also, we can see that there's a gap in between 1010 and 10101 and I don't want to fill this huge gap by extending it to say 1011 and so on..... Also, from the above extension, I don't want 1, 2, 3, 4... as my extension number. It should start with highest extension number and try to determine any existing gaps between the numbers. What would be the best way for me to do this? Thanks in advance

1000
1007
1010
10101
10102
10103
10104
10105
10106
1011
1012
1013
1014
1015
1016
1017
1022
1023
1027
1029
1031
1033
1034
1038
1041
1044
1045
1046
1050

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### jasonb75

##### Well-known Member
If the order is correct, then they are text strings, not valid numbers. This will make sequencing very difficult.

#### TheFastGuy

##### New Member
If the order is correct, then they are text strings, not valid numbers. This will make sequencing very difficult.
Ok I did manage to convert to text to a number. Now how would I identify the missing gaps?

#### jasonb75

##### Well-known Member
I'm not entirely sure of what you're expecting from this part,
I don't want 1, 2, 3, 4... as my extension number. It should start with highest extension number and try to determine any existing gaps between the numbers.
This method will list any missing numbers in sequence starting from the lowest number in the list, although, if I'm reading your post correctly, you already have this. In which case, I would need you to clarify what you do want rather than what you don't want.
Book1 (version 1).xlsb
ABC
110001000
210071001
310101002
4101011003
5101021004
6101031005
7101041006
8101051008
9101061009
1010111018
1110121019
1210131020
1310141021
1410151024
151016
161017
171022
181023
191027
201029
211031
221033
231034
241038
251041
261044
271045
281046
291050
Sheet5
Cell Formulas
RangeFormula
C1C1=MIN(A1:A29)
C2:C14C2=WORKDAY.INTL(C1,1,"0000000",\$A\$1:\$A\$29)

#### Fluff

##### MrExcel MVP, Moderator
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Cross posted at: Identify the missing sequence
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Replies
6
Views
233
Replies
1
Views
244
Replies
5
Views
233
Replies
1
Views
341
Replies
2
Views
313

1,141,873
Messages
5,709,104
Members
421,614
Latest member
RAB29

### 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