Identifying the missing gaps between number sequences

[TheFastGuy]

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

 

[jasonb75]

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

 

[TheFastGuy]

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]

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
	A	B	C
1	1000		1000
2	1007		1001
3	1010		1002
4	10101		1003
5	10102		1004
6	10103		1005
7	10104		1006
8	10105		1008
9	10106		1009
10	1011		1018
11	1012		1019
12	1013		1020
13	1014		1021
14	1015		1024
15	1016
16	1017
17	1022
18	1023
19	1027
20	1029
21	1031
22	1033
23	1034
24	1038
25	1041
26	1044
27	1045
28	1046
29	1050
Sheet5

Cell Formulas
Range	Formula
C1	C1	=MIN(A1:A29)
C2:C14	C2	=WORKDAY.INTL(C1,1,"0000000",$A$1:$A$29)

 

[Fluff]

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.

Be sure to follow & read the link at the end of the rule too!

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.