Help finding unassigned numbers in a list

[Nancy]

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?

 

[Gary''s Student]

Say we start with:

115
109
105
111
112
117
114
104
106
102
107
100
101
116
118
110
113
103
119

First we sort them:

100
101
102
103
104
105
106
107
109
110
111
112
113
114
115
116
117
118
119


Then in B2 enter:

=IF(A2-A1=1,"",A2-A1-1) and copy down. We now see:

100
101
102
103
104
105
106
107
109 1
110
111
112
113
114
115
116
117
118
119

This identifies 108 as the missing value.

 

[Pete_UK]

Could you post a few examples of what you have?

Pete

 

[Chip Pearson]

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)

 

[TheExcelAddict]

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 numbersnot
used in a string on numbers?

Copy the following formula down for each number you want to check. The
formula will return 1 for each number not used in the string of
numbers. I'm assuming that the 'string' of numbers are in one cell.

You can then use AutoFilter to extract these numbers.

=IF(ISERROR(FIND(E10,$E$4)),1,"")
=IF(ISERROR(FIND(string_to_search_for,string_to_search_in)),1,"")

More FREE Excel Tips at...
http://www.TheExcelAddict.com

 

[TheExcelAddict]

Copy the following formula down for each number you want to check.
The
formula will return 1 for each number not used in the string of
numbers. I'm assuming that the 'string' of numbers are in one cell.

You can then use AutoFilter to extract these numbers.


=IF(ISERROR(FIND(E10,$E$4)),1,"")
=IF(ISERROR(FIND(string_to_search_for,string_to_search_in)),1,"")


More FREE Excel Tips at...
http://www.TheExcelAddict.com

 

[T. Valko]

So, what you want to do is find missing numbers from a sequence?

Are the numbers simple numbers like 1,2,3,4,5 etc?

1,2,4,5

You want to know that 3 is missing, right?

If the numbers are simple numbers *and* the max number in the sequence is no
greater than the total rows available in a worksheet then it's fairly simple
but it could be a slow to calculate function if the sequence is large and
there are a lot of missing numbers.

So, we need more detail.