Identifying missing numerical values in a series

[SiH23]

In column A I have a series of numbers running from 1001 to 9078. These
numbers run in numerical order, but there are known missing values. For
example, the numbers may run 1000, 1001, 1005. In this instance the missing
numbers are 1002, 1003 and 1004. It is these missing numbers that I need to
identify from the column of numbers running from 1001 to 9078. Could these
missing numbers be placed in column C.

Any help would be greatly appreciated.

 

[Jacob Skaria]

Try the below array formula. This will be a bit slow...so try with a smaller
range for testing as below..Please note that this is an array formula. You
create array formulas in the same way that you create other formulas, except
you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula
Bar' you can notice the curly braces at both ends like "{=<formula>}"

=SMALL(IF(COUNTIF(A1:A800,ROW(101:907))=0,ROW(101:907)),ROW(A1))

 

[Per Jessen]

HI

This macro will do the job:

Sub FindMissing()
Dim FirstRow As Long
Dim LastRow As Long
Dim Counter As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row
FirstRow = 1

Application.ScreenUpdating = False
For r = FirstRow To LastRow - 1
If Cells(r, "A").Value + 1 <> Cells(r + 1, "A").Value Then
NextVal = Cells(r, "A").Value + 1
Do
Counter = Counter + 1
Cells(Counter, "C") = NextVal
NextVal = NextVal + 1
Debug.Print Cells(r + 1, "A").Value & ": " & NextVal
Loop Until Cells(r + 1, "A").Value = NextVal
End If
Next
Application.ScreenUpdating = True
End Sub

If you have not worked with macros before, hit ALT+F11 to open the VBA
editor > Insert > Module > Insert the code above in the code sheet and run
it.

Hopes this helps.
....
Per

 

[Jacob Skaria]

With your series try the below formula in cell C1 and copy down as required...

=SMALL(IF(COUNTIF(A1:A8078,ROW(1001:9078))=0,ROW(1001:9078)),ROW(A1))

 

[Minty Fresh]

Select the entire partial list in column a and give it a name.
([Insert] menu, selecting [Name] and then [Define])
For this example I chose the name 'par' for partial.
Enter '1001' in cell C1.
Enter the following formula in cell C2:
=IF(ISERROR(MATCH(C$1+ROW()-1,par,0)),C$1+ROW()-1,"")
fill down for 8070 rows
this will result in a list in column C that contains the numbers that are
not listed in column a, but there will be empty spaces in between the numbers.
to eliminate the spaces, select the entire column C
copy the entire column
with column C still selected, select [Paste Special] from the [Edit] menu
and select [Values] to replace the formulas in C with values
with C still selected, sort the column to move the cells with values to the
top of the stack.
Not pretty, but it works.

 

[SiH23]

Hi,

Many thanks for all your help. The array formula seems to identify the
number of missing numbers, but not the actual numbers themselves. Is there
anyway this can be done?

 

[T. Valko]

Try this array formula**.

Note that this may be "slow" to calculate.

Assume your data is in the range A2:A5000

Array entered** in C2:

=SMALL(IF(ISNA(MATCH(ROW($1001:$9078),A$2:A$5000,0)),ROW($1001:$9078)),ROWS(C$2:C2))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down until you start getting #NUM! errors meaning all missing numbers
have been returned.

Also note, this formula is vulnerable to row insertions in certain
locations. If you know for certain that you will never need to insert new
rows then no problem. However, if you might insert new rows then try this
version (still array entered):

=SMALL(IF(ISNA(MATCH(ROW(INDEX(A:A,1001):INDEX(A:A,9078)),A$2:A$5000,0)),ROW(INDEX(A:A,1001):INDEX(A:A,9078))),ROWS(C$2:C2))