How to Find Missing Numbers in a Sequence

[jacharya]

Suppose from 1 through 1000, please give possible number of ways

 

[bobhc]

Good day jacharya


If A1:A1000 contains your numbers, you could place the following formula in cell B2:


=IF(A2-A1=1,"","Missing Number Here")


and copy all the way down to cell B1000.


This would show as :-

1

2

3

4 Missing Number Here

6

7

8 Missing Number Here

10

11

12 Missing Number Here

14 Missing Number Here

 

[jacharya]

Hai, good suggest. But I need the missing number to be mentioned.

 

[lhkittle]

Hi jacharya,


Option Explicit


Sub MissNum()

Dim c As Range

For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row - 1)

If c.Offset(1, 0).Value - c.Value <> 1 Then

MsgBox "Missing Number " & c.Offset(1, 0) - 1

c.Offset(0, 1).Value = c.Offset(1, 0) - 1

End If

Next

End Sub


Regards,

Howard

 

[NARAYANK991]

Hi Acharya ,


I am giving below , the general algorithm for solving this problem ; please tailor it to your requirements.


We need to define some named ranges :


1. Let us start with the data ; suppose your data is like this :

[pre]

1
2
3
4
5
7
8
9
12
13
14
15
16
11

[/pre]
where the numbers need not be in sequence.


Let us call this range Data_Range.


2. Now suppose in a separate part of your worksheet , you have defined the following two cells :


Start_Number , which defines the first number which is supposed to be present in your data range


Step_Size , which defines how the sequence is supposed to increment ; if your sequence increases in steps of 1 , this will be 1.


3. Let us define a named range One_To_Rows , which has the following formula in the Refers To box in the Name Manager : =ROW(INDIRECT(Start_Number&":"&(ROWS(Data_Range))))


What this does is define an array of numbers , which starts from Start_Number , and goes up in steps of 1 , till the number of rows in Data_Range.


4. Define a named range called Final_List , which has the following formula in the Refers To box in the Name Manager : =(One_To_Rows-1)*Step_Size+1


What this does is define the required array of numbers , based on Start_Number and Step_Size.


5. Now , in any unused cell , enter the following formula , entered as an array formula , using CTRL SHIFT ENTER , and copy down as far as you want :


=IFERROR(INDEX(Final_List,SMALL(IF(ISNA(MATCH(Final_List,Data_Range,0)),One_To_Rows),ROW(A1))),"")


Narayan

 

[Haseeb A]

Try this Array Formula,


Assuming numbers are in A2:A1000 & list of numbers is 1:1000


In B2, with CTRL+SHIFT+ENTER


=SMALL(IF(ISNA(MATCH(ROW(A$2:A$1000)-ROW(A$2)+1,A$2:A$1000,0)),ROW(A$2:A$1000)-ROW(A$2)+1),ROWS(B$2:B2))


then copy down.

 

[NARAYANK991]

Hi Haseeb ,


Thanks for the hint ; my earlier formula can do away with the INDEX , to give essentially your formula :


=IFERROR(SMALL(IF(ISNA(MATCH(Final_List,Data_Range,0)),One_To_Rows),ROW(A1)),"")


Also , if we define another named range End_Number , which will have the last number in the sequence , in this case 1000 , then the above formula can be used as it is , changing the definition of the named range One_To_Rows ; the earlier definition was :

[pre]

=ROW(INDIRECT(Start_Number&":"&(ROWS(Data_Range))))
Change this to use End_Number :

=ROW(INDIRECT(Start_Number&":"&INT(End_Number/Step_Size)))

[/pre]
Narayan

 

[uma.vattikuti]

I would prefer the following steps:

1. fill a blank column with numbers 1 to 1000

2. Apply vlookup in an adjacent column using the existing data as table range.

3. Now filter the column with vlookup formula for #N/A to identify missing numbers in existing data.