Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Find missing numbers

  1. #1
    Board Regular
    Join Date
    Oct 2013
    Posts
    282
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Find missing numbers

    I have a range of numbers from, let's say, 1 to 10000. But there are numbers missing. What would be a solution to find the missing numbers and put them in a column?
    Using: Windows 10 + Microsoft Excel Professional 2016 (English version ! ! !)
    and also an Acer Chromebook with Google spreadsheet (Online).

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Find missing numbers

    Quote Originally Posted by strooman View Post
    I have a range of numbers from, let's say, 1 to 10000. But there are numbers missing. What would be a solution to find the missing numbers and put them in a column?
    A small sample would be very helpful...

    We have say in A2:A5...

    3
    5
    3
    6

    what would be the output if the numbers must come from 1 to 10?
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    Board Regular
    Join Date
    Sep 2014
    Posts
    215
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find missing numbers

    Let's say your range is in column A.

    Code:
    Dim Flag as Boolean
    
    For i = 1 to 10000
             Flag = False
    
             For j = 1 to ActiveSheet.UsedRange.Rows.Count
                     If Range("A" & j).Value = i Then
                            Flag = True
                            Exit For
                     End if
             Next
           
             If Flag = False
                 Range("B1").End(xldown).Offset(1,0).Value = i
             End If
    Next
    You must make sure there is something in B2 for this to work.

  4. #4
    Board Regular yesterdays's Avatar
    Join Date
    Oct 2014
    Location
    VietNam
    Posts
    337
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find missing numbers

    May be this :
    A B
    1 Missing
    2 1 8
    3 2 10
    4 3 11
    5 4 13
    6 5 14
    7 5
    8 6
    9 7
    10 9
    11 12
    12 15


    Formula in B2:
    =IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT("$"&MIN($A$2:$A$12)&":"&"$"&MAX($A$2:$A$12))),$A$2:$A$12,0))*1=1,ROW(INDIRECT("$"&MIN($A$2:$A$12)&":"&"$"&MAX($A$2:$A$12)))),ROW()-ROW($B$1)),"")

    Confirm with Ctrl + Shift + Enter
    This formula assume Number 1 and 10000 not missing ....
    Last edited by yesterdays; Nov 11th, 2014 at 05:25 AM.

  5. #5
    Board Regular Matt Rogers's Avatar
    Join Date
    Sep 2011
    Location
    77550
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find missing numbers

    I also have 2 options ...

    ABCDE
    131 34
    252 57
    334 39
    467 612
    589 814
    61012 1015
    71114 1117
    81315 1318
    91617 16
    101918 19
    11 20
    12

    CellFormula
    B1{=SMALL(IF(COUNTIF(A:A,ROW($1:$20))=0,ROW($1:$20)),ROW())}
    E1{=SMALL(IF(COUNTIF(D:D,ROW(INDIRECT("1:"&MAX(D:D)-MIN(D:D)+1))+MIN(D:D)-1)=0,ROW(INDIRECT("1:"&MAX(D:D)-MIN(D:D)+1))+MIN(D:D)-1),ROW())}
    Array-Formula!
    Ctrl+Shift + Enter
    Regards Matt
    ___________________________________________________________
    *The difference between Men and Boys is the price of their toys*

  6. #6
    Board Regular
    Join Date
    Oct 2013
    Posts
    282
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find missing numbers

    Quote Originally Posted by Aladin Akyurek View Post
    A small sample would be very helpful...

    We have say in A2:A5...
    Well, there are no duplicates and it is a series of consecutive numbers. So in this example from A1 to A23

    id
    3
    4
    5
    6
    8
    9
    10
    17
    20
    22
    23
    25
    26
    27
    30
    31
    32
    35
    37
    38
    39
    46

    Missing: 1,2,7,11,12,13,14,15,16,18,19,21,24,28,29,33,34,36,40,41,42,43.44.45

    For the sake of clarity the list in column A is much longer. We take 10.000 as the max.
    Using: Windows 10 + Microsoft Excel Professional 2016 (English version ! ! !)
    and also an Acer Chromebook with Google spreadsheet (Online).

  7. #7
    Board Regular yesterdays's Avatar
    Join Date
    Oct 2014
    Location
    VietNam
    Posts
    337
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find missing numbers

    I guess Other's solution are much better but I'm just want to learn so here is my solution:
    B2 : =IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT("$1:"&"$"&MAX(A:A))),A:A,0))*1=1,ROW(INDIRECT("$1:"&"$"&MAX(A:A)))),ROW()-ROW($B$1)),"")
    Last edited by yesterdays; Nov 11th, 2014 at 05:46 AM.

  8. #8
    Board Regular Matt Rogers's Avatar
    Join Date
    Sep 2011
    Location
    77550
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find missing numbers

    Hi yesterdays.

    I guess Other's solution are much better ...
    New ideas need the country!
    Regards Matt
    ___________________________________________________________
    *The difference between Men and Boys is the price of their toys*

  9. #9
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,474
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find missing numbers

    Just a small point, but a range reference generated via INDIRECT does not require any absolute references (i.e. $), since it will be in any case remain unchanged as the formula is copied to further cells.

    Also, the use of the unqualified ROW() as the k parameter for SMALL is not a very rigorous choice. Much better is to use a construction involving ROWS, not ROW, e.g. ROWS($1:1). See here for details:

    ROW vs ROWS for consecutive integer generation | EXCELXOR

    Regards
    Advanced Excel Techniques: http://excelxor.com/

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Find missing numbers

    Assuming you do not need the list to be "live", and if you are up for a macro solution, then I think this should work (it has not been tested against 10,000 entries)...
    Code:
    Sub MissingNumbers()
      Dim X As Long, Max As Long, Given As Variant, Missing As Variant
      Given = Range("A1", Cells(Rows.Count, "A").End(xlUp))
      ReDim Missing(1 To Given(UBound(Given), 1))
      For X = 1 To UBound(Missing)
        Missing(X) = X
      Next
      For X = 1 To UBound(Given)
        Missing(Given(X, 1)) = ""
      Next
      Missing = Application.Transpose(Split(Application.Trim(Join(Missing))))
      Range("B1").Resize(UBound(Missing)) = Missing
    End Sub
    Note: The above code assumes the list you have in Column A starts at Row 1... if you really have a header with the text "id", I can modify the code to account for it.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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