1. ## 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!

2. ## Re: Missing Numbers (97/2000)

A non-elegant 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.

3. ## 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.

4. ## Re: Missing Numbers (97/2000)

You got me there Hans! Didn't think about that.

5. ## 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.

6. ## 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>

7. ## Re: Missing Numbers (97/2000)

Hi buckshot,

=IF(A2-A1=1,"",A2-A1-1&" value(s) missing")
which tles you how many numbers are missing between the rows being evaluated, or
=IF(A2-A1=1,"",IF(A2-A1=2,"#"&A2-1&" missing","#"&A1+1& " to #"&A2-1&" 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 n-1rows.

Cheers

8. ## 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

9. ## Re: Missing Numbers (97/2000)

Assuming data is ordered in A1:An

Then B1

=IF(A2<>A1+1,IF(A2-A1=2,A1+1,A1+1 & "-" & A2-1),"")

Filled down will work to highlight gaps

10. ## 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>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•