Table of contents

  1. Missing numbers (array formula)
  2. Missing numbers (vba)

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

Download excel sample file for this tutorial.
missing-values-in-a-column.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

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", _
Default:=Selection.Address, Type:=8)
StartV = InputBox("Start value:")
EndV = InputBox("End value:")
On Error GoTo 0
Set WS = Sheets.Add
WS.Range("A1:A" & rng.Rows.CountLarge).Value = rng.Value
With WS.Sort
    .SortFields.Add Key:=WS.Range("A1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range("A1:A" & rng.Rows.CountLarge)
    .Header = xlNo
    .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

Download *.xlsm file

Find-missing-values-version2.xlsm