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).
Let's say your range is in column A.
You must make sure there is something in B2 for this to work.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
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.
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
Regards Matt
___________________________________________________________
*The difference between Men and Boys is the price of their toys*
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).
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.
Hi yesterdays.
New ideas need the country!I guess Other's solution are much better ...
Regards Matt
___________________________________________________________
*The difference between Men and Boys is the price of their toys*
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/
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
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See Part B here.
Like this thread? Share it with others