Thanks:  0
Likes:  0

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

2. ## Re: Find missing numbers

Originally Posted by 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?
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?

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

5. ## Re: Find missing numbers

I also have 2 options ...

 A B C D E 1 3 1 3 4 2 5 2 5 7 3 3 4 3 9 4 6 7 6 12 5 8 9 8 14 6 10 12 10 15 7 11 14 11 17 8 13 15 13 18 9 16 17 16 10 19 18 19 11 20 12

 Cell Formula 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

6. ## Re: Find missing numbers

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.

7. ## 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)),"")

8. ## Re: Find missing numbers

Hi yesterdays.

I guess Other's solution are much better ...
New ideas need the country!

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

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

## User Tag List

#### Posting Permissions

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