Find missing numbers

strooman · Nov 11, 2014

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?

Aladin Akyurek · Nov 11, 2014

strooman said:
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?

ChrisBM · Nov 11, 2014

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.

yesterdays · Nov 11, 2014

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

Matt Rogers · Nov 11, 2014

I also have 2 options ...

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='6' style='font-family:Arial; color:#000000; background-color:#FFFFFF; font-size:10px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='45,75pt'><col width='45,75pt'><col width='7,5pt'><col width='45,75pt'><col width='45,75pt'></colgroup><tr style='background-color:#cacaca'><td> </td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td><td align='middle'>D</td><td align='middle'>E</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='right' style='font-family:Calibri; ' >3</td><td align='right' style='font-family:Calibri; ' >1</td><td align='middle' style='font-family:Calibri; ' > </td><td align='right' style='font-family:Calibri; ' >3</td><td align='middle' style='font-family:Calibri; ' >4</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='right' style='font-family:Calibri; ' >5</td><td align='right' style='font-family:Calibri; ' >2</td><td align='middle' style='font-family:Calibri; ' > </td><td align='right' style='font-family:Calibri; ' >5</td><td align='middle' style='font-family:Calibri; ' >7</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='right' style='font-family:Calibri; ' >3</td><td align='right' style='font-family:Calibri; ' >4</td><td align='middle' style='font-family:Calibri; ' > </td><td align='right' style='font-family:Calibri; ' >3</td><td align='middle' style='font-family:Calibri; ' >9</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='right' style='font-family:Calibri; ' >6</td><td align='right' style='font-family:Calibri; ' >7</td><td align='middle' style='font-family:Calibri; ' > </td><td align='right' style='font-family:Calibri; ' >6</td><td align='middle' style='font-family:Calibri; ' >12</td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='right' style='font-family:Calibri; ' >8</td><td align='right' style='font-family:Calibri; ' >9</td><td align='middle' style='font-family:Calibri; ' > </td><td align='right' style='font-family:Calibri; ' >8</td><td align='middle' style='font-family:Calibri; ' >14</td></tr><tr><td style='background-color:#cacaca' align='middle'>6</td><td align='right' style='font-family:Calibri; ' >10</td><td align='right' style='font-family:Calibri; ' >12</td><td align='middle' style='font-family:Calibri; ' > </td><td align='right' style='font-family:Calibri; ' >10</td><td align='middle' style='font-family:Calibri; ' >15</td></tr><tr><td style='background-color:#cacaca' align='middle'>7</td><td align='right' style='font-family:Calibri; ' >11</td><td align='right' style='font-family:Calibri; ' >14</td><td align='middle' style='font-family:Calibri; ' > </td><td align='right' style='font-family:Calibri; ' >11</td><td align='middle' style='font-family:Calibri; ' >17</td></tr><tr><td style='background-color:#cacaca' align='middle'>8</td><td align='right' style='font-family:Calibri; ' >13</td><td align='right' style='font-family:Calibri; ' >15</td><td align='middle' style='font-family:Calibri; ' > </td><td align='right' style='font-family:Calibri; ' >13</td><td align='middle' style='font-family:Calibri; ' >18</td></tr><tr><td style='background-color:#cacaca' align='middle'>9</td><td align='right' style='font-family:Calibri; ' >16</td><td align='right' style='font-family:Calibri; ' >17</td><td align='middle' style='font-family:Calibri; ' > </td><td align='right' style='font-family:Calibri; ' >16</td><td align='middle' style='font-family:Calibri; ' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>10</td><td align='right' style='font-family:Calibri; ' >19</td><td align='right' style='font-family:Calibri; ' >18</td><td align='middle' style='font-family:Calibri; ' > </td><td align='right' style='font-family:Calibri; ' >19</td><td align='middle' style='font-family:Calibri; ' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>11</td><td align='right' style='font-family:Calibri; ' > </td><td align='right' style='font-family:Calibri; ' >20</td><td align='middle' style='font-family:Calibri; ' > </td><td align='middle' style='font-family:Calibri; ' > </td><td align='middle' style='font-family:Calibri; ' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>12</td><td align='right' style='font-family:Calibri; ' > </td><td align='right' style='font-family:Calibri; ' > </td><td align='middle' style='font-family:Calibri; ' > </td><td align='middle' style='font-family:Calibri; ' > </td><td align='middle' style='font-family:Calibri; ' > </td></tr></table> <table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Arial; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Cell</td><td>Formula</td></tr><tr><td>B1</td><td>{=SMALL(IF(COUNTIF(A:A,ROW($1:$20))=0,ROW($1:$20)),ROW())}</td></tr><tr><td>E1</td><td>{=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())}</td></tr></table><table style='font-family:Arial; font-size:8pt; background-color:#FFFFFF'><tr><td style='font-weight:bold'>Array-Formula!</td></tr><tr><td>Ctrl+Shift + Enter </td></tr><tr><td></td></tr></table>

strooman · Nov 11, 2014

Aladin Akyurek said:
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.

yesterdays · Nov 11, 2014

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

Matt Rogers · Nov 11, 2014

Hi yesterdays.

I guess Other's solution are much better ...

New ideas need the country!

XOR LX · Nov 11, 2014

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

Rick Rothstein · Nov 11, 2014

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.

Find missing numbers

strooman

Active Member

Excel Facts

Aladin Akyurek

MrExcel MVP

ChrisBM

Board Regular

yesterdays

Active Member

Matt Rogers

Well-known Member

strooman

Active Member

yesterdays

Active Member

Matt Rogers

Well-known Member

XOR LX

Well-known Member

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

Find missing numbers

Active Member

Excel Facts

MrExcel MVP

Board Regular

Active Member

Well-known Member

Active Member

Active Member

Well-known Member

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock