Sometimes you may want to identify missing numbers in a sequence in Excel such as product ID numbers. One way would be to manually scroll through the dataset and identify the missing numbers. However, this would be quite a tedious, time-consuming, and error-prone task especially if you are working with large datasets.
In this tutorial we will explain two methods that can be used to find missing numbers in sequence in Excel more easily and efficiently:
Method 1 – Use an Excel formula
We can use an Excel formula that combines the SMALL, IF, COUNTIF, and ROW functions.
We will use the following dataset to demonstrate how this method can be used:
Step 1 – Enter the formula:
|
1 |
=SMALL(IF(COUNTIF($A$1:$A$6,ROW($21:$32))=0,ROW($21:$32),""),ROW(A1)) |
in cell C1:
Step 2 – The formula is an array formula so enter it by pressing CTRL + SHIFT + ENTER:
Step 3 – Using the Fill Handle, drag the formula down to cell C6:
All the missing numbers in the sequence will be generated.
The logic of the Formula
The formula counts the numbers in the range A1:A6 that match the numbers generated by the ROW($21:$32) which are {21;22;23;24;25;26;27;28;29;30;31;32}. If they are missing, that is, =0, it gives a list of them. If they are not missing, that is, not =0, it returns an empty string (“”), and then returns the first smallest missing value in the current cell.
The ROW(A1) part of the formula is relative meaning as it is copied down the column C, it becomes ROW(A2) which =2, and returns the second smallest value, ROW(A3) which =3 and returns the third smallest value, and so on.
The functions used in the formula
SMALL function
It returns the smallest nth value in a range, in the form of the first smallest value, the second smallest value, the third smallest value, and so on.
IF function
It tests for counts of 0. If it is TRUE, that is 0, it means the number is missing and so it returns the corresponding value from the array generated by ROW(21:32).
COUNTIF function
It counts the numbers in the range A1:A6 that match the range of values returned by the ROW function.
ROW function
It returns the row number of a cell reference.
Stepping through the formula
We will step through the formula:
|
1 |
=SMALL(IF(COUNTIF($A$1:$A$6,ROW($21:$32))=0,ROW($21:$32),""),ROW(A1)) |
to see how it works.
Step 1 – The values in range A1:A6 and ROW(21:32) are returned:
|
1 |
=SMALL(IF(COUNTIF({21;27;25;28;30;32},ROW({21;22;23;24;25;26;27;28;29;30;31;32}))=0,ROW($21:$32),""),ROW(A1)) |
Step 2 – The COUNTIF function returns the counts of values in the array returned by ROW(21:32), that are found in the range A1:A6:
|
1 |
=SMALL(IF({1;0;0;0;1;0;1;1;0;1;0;1})=0,ROW($21:$32),""),ROW(A1)) |
This means that there is 1 count of 21, 0 count of 22, and so on in the range A1:A6.
Step 3 – The IF function returns TRUE if the number in the resultant array =0 and FALSE if it doesn’t:
|
1 |
=SMALL(IF({FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE},{21;22;23;24;25;26;27;28;29;30;31;32),""),ROW(A1)) |
Step 4 – The IF function evaluates the value_if_true and value_if_false values:
|
1 |
=SMALL({"";22;23;24;"";26;"";"";29;"";31;""},ROW(A1)) |
We now have the list of missing numbers.
Step 5 – The ROW(A1) function evaluates to 1:
|
1 |
=SMALL({"";22;23;24;"";26;"";"";29;"";31;""},1) |
Step 6 – The SMALL function returns the first smallest value in the array which is =22.
The next smallest value is =23 and so on.
The Formula method generally works well but it has the limitation of working only with whole numbers that are greater than 0.
To overcome this limitation we have to turn to the next method of using Excel VBA code.
Method 2 – Use Excel VBA Code
We will use the following dataset to demonstrate how VBA code can be used to find missing numbers in sequence in Excel:
Step 1 – Press Alt + F11 to open the Visual Basic Editor (VBE). Alternatively, you can click Developer >> Visual Basic:
Step 2 – In the VBAProject window right click your workbook and click Insert >> Module:
Step 3 – Enter the following code in the new module. Note that comments that are not executable are preceded by an apostrophe (‘). The comments explain what the code is doing:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
Sub FindMissingNumbers() Dim InputRange As Range, OutputRange As Range, NumberFound As Range Dim LowerNum As Double, UpperNum As Double, count_i As Double, count_j As Double Dim NumRows As Long, NumColumns As Long Dim Horizontal As Boolean 'Default is to output the results into a column Horizontal = FALSE On Error GoTo ErrorHandler 'Ask for the range to check Set InputRange = Application.InputBox(Prompt:="Select a range To check :", _ Title:="Find missing numbers", _ Default:=Selection.Address, Type:=8) 'Find the lowest and highest numbers in the range/sequence LowerNum = WorksheetFunction.Min(InputRange) UpperNum = WorksheetFunction.Max(InputRange) 'Ask where the output is to go Set OutputRange = Application.InputBox(Prompt:="Select where you want the result To go :", _ Title:="Select cell For Results", _ Default:=Selection.Address, Type:=8) 'Check the number of rows and columns in the output range NumRows = OutputRange.Rows.Count NumColumns = OutputRange.Columns.Count 'If there are more columns selected than rows, the output is to go horizontally If NumRows < NumColumns Then Horizontal = TRUE 'Reset the number of rows to 1 so that output is into the first row NumRows = 1 Else 'Reset the number of columns to 1 so that output is into the first column NumColumns = 1 End If 'Initalise counter and loop through sequence from lowest to highest value count_j = 1 For count_i = LowerNum To UpperNum 'Search for the current value (count_i) Set NumberFound = InputRange.Find(count_i, LookIn:=xlValues, LookAt:=xlWhole) 'If it's not found, we have a missing number in the sequence If NumberFound Is Nothing Then 'Output the missing number to the sheet If Horizontal Then OutputRange.Cells(NumRows, count_j).Value = count_i count_j = count_j + 1 Else OutputRange.Cells(count_j, NumColumns).Value = count_i count_j = count_j + 1 End If End If Next count_i Exit Sub ErrorHandler: If InputRange Is Nothing Then MsgBox "ERROR : No input range specified." Exit Sub End If If OutputRange Is Nothing Then MsgBox "ERROR : No output cell specified." Exit Sub End If MsgBox "An Error has occurred. The macro will end." End Sub |
Step 4 – Click the Save button:
Step 5 – Save the workbook as a .xlsm file:
Code Explanation
The code uses VBA’s FIND method on the input range you specify to look for the missing numbers in sequence.
When the code is executed, Excel prompts asks for the input range in which to search for missing numbers. Use the mouse to select the range.
You will then be prompted for the output range where the missing numbers will be listed.
Code Execution
Step 1 – Switch to the current workbook by pressing Alt + F11. Alternatively, click on the View Microsoft Excel button:
Step 2 – Press Alt + F8 to open the Macro dialog box and select the FindMissingNumbers macro and click on the Run button:
Step 3 – In the Find missing numbers dialog box, enter the input range by selecting using the mouse and then click OK:
Step 4 – Enter the output range and click OK:
The missing numbers will appear in the output range you selected:
