Here's another one...
Array entered** in F1:
=SMALL(IF(ISNA(MATCH(ROW(A$1:A$500),D$1
$2000,0)),ROW(A$1:A$500)),ROWS(F$1:F1))
** 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 get #NUM! errors meaning all missing numbers have been
returned. This is kind of slow to calculate.
Here's a macro from JMB that will do this. Place the code in a general
module.
Sub FindMissing()
Dim lngUpper As Long
Dim lngLower As Long
Dim i As Long
Dim rngData As Range
Dim lngcount As Long
Set rngData = Range("D1
2000") 'change as needed
lngLower = 1 'start of sequence
lngUpper = 500 'end of sequence
lngcount = 1
For i = lngLower To lngUpper
If Not IsNumeric(Application.Match(i, _
rngData, 0)) Then
Range("F" & lngcount).Value = i 'sets output to column F
lngcount = lngcount + 1
End If
Next i
End Sub
