Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    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!

  2. #2
    5 Star Lounger
    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 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. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  4. #4
    5 Star Lounger
    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  6. #6
    5 Star Lounger
    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>


  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Missing Numbers (97/2000)

    Hi buckshot,

    How about using something like:
    =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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

  9. #9
    5 Star Lounger
    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(A2-A1=2,A1+1,A1+1 & "-" & A2-1),"")

    Filled down will work to highlight gaps

  10. #10
    5 Star Lounger
    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>


Posting Permissions

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