You can use a VBA procedure like the following:
Sub ListMissing()
Dim R As Range
Dim Dest As Range
Dim LastRow As Long
Dim N As Long
Set R = Range("A1") '<<< cell where numbers start
With R.Worksheet
LastRow = .Cells(.Rows.Count, R.Column).End(xlUp).Row
End With
Set Dest = Range("H10") '<<< cell where missing numbers are
written
Set R = R(2, 1)
Do Until R.Row >= LastRow
For N = R.Value + 1 To R(2, 1).Value - 1
Dest.Value = N
Set Dest = Dest(2, 1)
Next N
Set R = R(2, 1)
Loop
End Sub
Change the lines marked with "<<<" to the approiate cell references. R
is the first cell of the master list of numbers. Dest is the cell
where the missing numbers are to be written. This code assumes that
the numbers in the master list are integers (no fractional portion)
and that they are in ascending order. If you want to select the range
of cells in the master list, get rid of
With R.Worksheet
LastRow = .Cells(.Rows.Count, R.Column).End(xlUp).Row
End With
and replace it with
With Selection
Set R = .Cells(1, 1)
LastRow = .Cells(.Cells.Count).Row
End With
Then, select a range in the master list (one column, many rows) and
run the code.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)