Results 1 to 10 of 10
Thread: Missing Numbers (97/2000)

20030212, 15:03 #1
 Join Date
 Dec 2002
 Posts
 192
 Thanks
 0
 Thanked 0 Times in 0 Posts
Missing Numbers (97/2000)
Hi All !
I have a list of I D numbers 1 through 999 in column A. How can I find the missing numbers in my list. Example, column A might be 1,3,5,6,7,9. I need excel to say you are missing 2,4 and 8. Thank You for all your help!

20030212, 15:13 #2
 Join Date
 Feb 2001
 Location
 Dallas plus 20 miles or so, Texas, USA
 Posts
 876
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Missing Numbers (97/2000)
A nonelegant solution would be to put in the column next to A, starting in cell b2 for example,
=IF(A2<>A1+1,A1+1,"")
which will place the missing number in the column to the right of A, a more dignified approach might be used if you were to consider VBA.

20030212, 15:21 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Missing Numbers (97/2000)
I was thinking along that line too, but it won't catch larger gaps in the series: for instance, if you have 1,2,5,6 it will flag 3 as missing, but not 4.

20030212, 15:34 #4
 Join Date
 Feb 2001
 Location
 Dallas plus 20 miles or so, Texas, USA
 Posts
 876
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Missing Numbers (97/2000)
You got me there Hans! Didn't think about that.

20030212, 15:56 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Missing Numbers (97/2000)
Your numbers are in column A.
Select B1:B999 (or another range of 999 cells in a column).
The following formula is an array formula; it must be confirmed by Ctrl+Shift+Enter instead of just Enter:
=IF(ISNA(MATCH(ROW(1:999),A1:A999,0)),ROW(1:999)," ")
Since there are missing numbers, your range in column A is actually smaller than A1:A999 but that doesn't matter.
If you wish, you can copy the selected cells and paste them as values; then you can sort them to obtain a contiguous range.

20030212, 16:32 #6
 Join Date
 Feb 2001
 Location
 Dallas plus 20 miles or so, Texas, USA
 Posts
 876
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Missing Numbers (97/2000)
Like it Hans, but this has been wreaking havoc in my brain, so....
<pre>Sub FindMissing()
Dim cell As Range
Dim b As String
Dim c As Integer
Dim a As Integer
Application.ScreenUpdating = False
For a = 1 To 999
c = 0
For Each cell In ActiveSheet.Range("a1:a999")
If cell.Value = a Then
c = 1
Exit For
End If
Next
If c = 0 Then b = b + Str(a)
Next
ActiveSheet.Range("d1").Value = b
Application.ScreenUpdating = True
End Sub</pre>

20030213, 02:16 #7
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,240
 Thanks
 2
 Thanked 470 Times in 387 Posts
Re: Missing Numbers (97/2000)
Hi buckshot,
How about using something like:
=IF(A2A1=1,"",A2A11&" value(s) missing")
which tles you how many numbers are missing between the rows being evaluated, or
=IF(A2A1=1,"",IF(A2A1=2,"#"&A21&" missing","#"&A1+1& " to #"&A21&" missing"))
which tells you what numbers are missing between the rows being evaluated,
where your data is in A1 to A(n), and you place the formula on, say, B2 and copy it down for n1rows.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20030213, 03:06 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Missing Numbers (97/2000)
How about making a list of numbers from 1 to 999. and then Use MATCH with each one to see if it is in the column. Anyone giving an error is NOT in the list.
Steve

20030213, 03:18 #9
 Join Date
 Oct 2002
 Location
 Wellington, Wellington, New Zealand
 Posts
 621
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Missing Numbers (97/2000)
Assuming data is ordered in A1:An
Then B1
=IF(A2<>A1+1,IF(A2A1=2,A1+1,A1+1 & "" & A21),"")
Filled down will work to highlight gaps

20030213, 04:06 #10
 Join Date
 Feb 2001
 Location
 Dallas plus 20 miles or so, Texas, USA
 Posts
 876
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Missing Numbers (97/2000)
While at work, I thought about it and came up with this...lists all numbers from 1  999 in column D which are not listed in column "A:
<pre>Option Explicit
Sub FindMissing()
Dim cell As Range
Dim b As Integer
Dim c As Integer
Dim a As Integer
Application.ScreenUpdating = False
ActiveSheet.Range("d1").Value = "Missing Numbers"
b = 2
For a = 1 To 999
c = 0
For Each cell In Range(Cells(1, 1), Cells(Range("a:a").End(xlDown).Row, 1))
If cell.Value = a Then
c = 1
Exit For
End If
Next
If c = 0 Then
ActiveSheet.Cells(b, 4).Value = a
b = b + 1
End If
Next
Application.ScreenUpdating = True
End Sub</pre>