Identifying the missing gaps between number sequences

TheFastGuy

New Member
Joined
Mar 19, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,607
Office Version
  1. 365
Platform
  1. Windows
If the order is correct, then they are text strings, not valid numbers. This will make sequencing very difficult.
 

TheFastGuy

New Member
Joined
Mar 19, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
13,607
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
62,666
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,864
Messages
5,709,066
Members
421,611
Latest member
Lisa W

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