Find missing numbers

[strooman]

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?

 

[yesterdays]

Hi Matt Rogers.
Thank you ( actually I don't understand what is that mean....could you please explain ? I'm not so good with English)

Hi XOR LX

Thank you too, that's another thing to learn

 

[Aladin Akyurek]

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.

If 10,000 is the upperbound and the sequence is supposed to start at 1, define SEQ as referring to:

=ROW(INDIRECT("1:10000"))

C2, control+shift+enter, not just enter, and copy down:

=SMALL(IF(1-ISNUMBER(MATCH(SEQ,$A$2:$A$23,0)),SEQ),ROWS($C$2:C2))

If this slows your workbook, a different option is a set up where you can invoke Advanced Filter.

 

[strooman]

Well, that's a lot of different solutions in a very short time. This will get me going. I like the variety of the solutions, VBA versus formula. I noticed that some solutions are heavy on the processor, but give me some time to finetune that.
Thanks guys for helping me with this one.

 

[Rick Rothstein]

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)...

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.

In thinking about it, I believe the code below will be faster than the code I posted earlier (shown above)...

Sub MissingNumbers()
  Dim X As Long, Y As Long, Z 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), 1 To 1)
  Z = 1
  For X = 1 To UBound(Missing)
    If X <> Given(Z, 1) Then
      Y = Y + 1
      Missing(Y, 1) = X
    Else
      Z = Z + 1
    End If
  Next
  Range("B1").Resize(UBound(Missing)) = Missing
End Sub

 

[strooman]

Whowwww mister Rothstein. That was fast as lightning. I could hardly blink my eyes and it was done ! ! !
For short series of numbers the formulas are fine but for large series I recommand this one. Thanks.

 

[Legacy 306534]

I have this code that fills down in the row the missing Nr.

Sub MissingNr()
Dim i As Long
Dim j As Long

i = 1
j = 1

Do Until i = 11
      
    If Range("A" & j).Value = i Then
    j = j + 1
    i = i + 1
    
 Else: Range("A1").End(xlDown).Offset(1, 0).Value = i
    
  i = i + 1
  
  End If

    Loop
       
End Sub