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.
In thinking about it, I believe the code below will be faster than the code I posted earlier (shown above)...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)...
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.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
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
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