Missing numbers (array formula)

Question: I want to find missing numbers in a range?

Answer: C1 and C2 contain the range boundaries.

Array Formula in B5

=SMALL(IF(ISERROR(MATCH(\$C\$1+ROW(OFFSET(\$A\$1, 0, 0, \$C\$2-\$C\$1+1))-1, \$A\$2:\$A\$6, 0)), \$C\$1+ROW(OFFSET(\$A\$1, 0, 0, \$C\$2-\$C\$1+1))-1), ROW(A1))

How to create an array formula

1. Select cell B5
2. Copy / Paste array formula
3. Press and hold Ctrl + Shift
4. Press Enter
5. Release all keys

How to copy array formula

1. Copy cell B5
2. Select cell B6:B11
3. Paste

missing-values-in-a-column.xlsx
(Excel 2007 Workbook *.xlsx)

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference)
returns the rownumber of a reference

SMALL(array,k)
Returns the k-th smallest row number in this data set.

OFFSET(reference,rows,columns,[height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference.

Missing numbers (vba)

The macro demonstrated here let´s you select a cell range (values must be in a single column), start and end number.

A new sheet is created, values are sorted in the first column. The second column (B) contains all missing values.

VBA

```Sub Missingvalues()
Dim rng As Range
Dim rng1 As Range
Dim StartV As Double, EndV As Double, i As Double, j As Single
Dim k() As Double
Dim WS As Worksheet
ReDim k(0)
On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select a range:", _
Title:="Extract missing values", _
StartV = InputBox("Start value:")
EndV = InputBox("End value:")
On Error GoTo 0
WS.Range("A1:A" & rng.Rows.CountLarge).Value = rng.Value
With WS.Sort
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A1:A" & rng.Rows.CountLarge)
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
MsgBox "test"
Set rng1 = WS.Range("A1:A" & rng.Rows.CountLarge)
For i = StartV To EndV
On Error Resume Next
j = Application.Match(i, rng1)
If Err = 0 Then
If rng1(j, 1) <> i Then
k(UBound(k)) = i
ReDim Preserve k(UBound(k) + 1)
End If
Else
k(UBound(k)) = i
ReDim Preserve k(UBound(k) + 1)
End If
On Error GoTo 0
Application.StatusBar = i
Next i
WS.Range("B1") = "Missing values"
WS.Range("B2:B" & UBound(k) + 1) = Application.Transpose(k)
End Sub
```