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)




On Fri, 21 Aug 2009 08:59:01 -0700, Nancy
wrote:

I have a large item master list and would like to find numbers that have not
been assigned. Is there a simple formula I can use to pull out numbers not
used in a string on numbers?