## Identify missing numbers in a column

The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower boundary is specified in cell E2 and the upper boundary is in cell E3. The ROW function has a limit of 1 048 576 so the number of values between the lower and upper boundary can't be more than 1 048 576.

If this limit won't work for you then later in this article you will find a macro that doesn't have this limit.

**Array Formula in D6**

**How to create an array formula**

- Select cell D6
- Copy / Paste array formula
- Press and hold Ctrl + Shift
- Press Enter
- Release all keys

**How to copy array formula**

- Copy cell D6
- Paste to cells below as far as needed.

### Explaining formula in cell B5

#### Step 1 - Build cell reference

The OFFSET function lets you create a cell reference with the same size as there are numbers between 2000000 and 2000010.

OFFSET($B$2,0,0,$E$3-$E$2+1)

becomes

OFFSET($B$2,0,0,2000010-2000000+1)

becomes

OFFSET($B$2,0,0,11)

and returns $B$2:$B$12

#### Step 2 - Convert cell reference to row numbers

The ROW function creates an array of row numbers based on the cell reference.

ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2

becomes

ROW($B$2:$B$12)-2

becomes

{2;3;4;5;6;7;8;9;10;11;12}-2

and returns

{0;1;2;3;4;5;6;7;8;9;10}

#### Step 3 - Add start number to array

$E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2

becomes

$E$2+{0;1;2;3;4;5;6;7;8;9;10}

becomes

2000000+{0;1;2;3;4;5;6;7;8;9;10}

and returns

{2000000;2000001;2000002;2000003;2000004;2000005;2000006;2000007;2000008;2000009;2000010}

This is the list we need to figure out which values are missing.

#### Step 4 - Which values exist?

The MATCH function finds the relative position of each number in the array in cell range $B$3:$B$7, it returns an error if not found.

MATCH($E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2,$B$3:$B$7,0)

becomes

MATCH({2000000;2000001;2000002;2000003;2000004;2000005;2000006;2000007;2000008;2000009;2000010},$B$3:$B$7,0)

becomes

MATCH({2000000;2000001;2000002;2000003;2000004;2000005;2000006;2000007;2000008;2000009;2000010},{2000001;2000003;2000004;2000007;2000008},0)

and returns

{#N/A;1;#N/A;2;3;#N/A;#N/A;4;5;#N/A;#N/A}

#### Step 5 - Identify errors

The ISERROR function returns TRUE if value is an error and FALSE if not.

ISERROR(MATCH($E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2,$B$3:$B$7,0))

becomes

ISERROR({#N/A;1;#N/A;2;3;#N/A;#N/A;4;5;#N/A;#N/A})

and returns

{TRUE;FALSE; TRUE;FALSE; FALSE;TRUE; TRUE;FALSE; FALSE;TRUE; TRUE}.

#### Step 6 - Replace errors with numbers

The IF function converts TRUE to corresponding number.

IF(ISERROR(MATCH($E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2,$B$3:$B$7,0)),$E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2)

becomes

IF({TRUE;FALSE; TRUE;FALSE; FALSE;TRUE; TRUE;FALSE; FALSE;TRUE; TRUE},$E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2)

becomes

IF({TRUE;FALSE; TRUE;FALSE; FALSE;TRUE; TRUE;FALSE; FALSE;TRUE; TRUE},{2000000;2000001;2000002;2000003;2000004;2000005;2000006;2000007;2000008;2000009;2000010})

and returns

{2000000; FALSE; 2000002; FALSE; FALSE; 2000005; 2000006; FALSE; FALSE; 2000009; 2000010}

#### Step 7 - Extract k-th smallest number

The SMALL function extracts the k-th smallest number, this makes the formula return a value in a cell each.

SMALL(IF(ISERROR(MATCH($E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2,$B$3:$B$7,0)),$E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2),ROW(A1))

becomes

SMALL({2000000; FALSE; 2000002; FALSE; FALSE; 2000005; 2000006; FALSE; FALSE; 2000009; 2000010},ROW(A1))

becomes

SMALL({2000000; FALSE; 2000002; FALSE; FALSE; 2000005; 2000006; FALSE; FALSE; 2000009; 2000010},1)

and returns 2000000 in cell D6.

### Missing numbers (vba)

The macro demonstrated here let´s you select a cell range (values must be in a single column), start and end number.

A new sheet is created, values are sorted in the first column. The second column (B) contains all missing values.

### VBA

Sub Missingvalues() Dim rng As Range Dim rng1 As Range Dim StartV As Double, EndV As Double, i As Double, j As Single Dim k() As Double Dim WS As Worksheet ReDim k(0) On Error Resume Next Set rng = Application.InputBox(Prompt:="Select a range:", _ Title:="Extract missing values", _ Default:=Selection.Address, Type:=8) StartV = InputBox("Start value:") EndV = InputBox("End value:") On Error GoTo 0 Set WS = Sheets.Add WS.Range("A1:A" &amp;amp;amp;amp;amp; rng.Rows.CountLarge).Value = rng.Value With WS.Sort .SortFields.Add Key:=WS.Range("A1"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SetRange Range("A1:A" &amp;amp;amp;amp;amp; rng.Rows.CountLarge) .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With MsgBox "test" Set rng1 = WS.Range("A1:A" &amp;amp;amp;amp;amp; rng.Rows.CountLarge) For i = StartV To EndV On Error Resume Next j = Application.Match(i, rng1) If Err = 0 Then If rng1(j, 1) &amp;amp;amp;amp;lt;&amp;amp;amp;amp;gt; i Then k(UBound(k)) = i ReDim Preserve k(UBound(k) + 1) End If Else k(UBound(k)) = i ReDim Preserve k(UBound(k) + 1) End If On Error GoTo 0 Application.StatusBar = i Next i WS.Range("B1") = "Missing values" WS.Range("B2:B" &amp;amp;amp;amp;amp; UBound(k) + 1) = Application.Transpose(k) End Sub

This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]

This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

In this post I am going to show how to create a new sheet for each airplane using vba. The […]

This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]

This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]

In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]

What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]

This article describes how to create an interactive chart, the user may press with left mouse button on a button […]

This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]

Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]

Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]

Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]

In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]

This article explains how to hide a specific image in Excel using a shape as a button. If the user […]

This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]

Today I would like to share with you these small event handler procedures that make it easier for you to […]

This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]

This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]

Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]

Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]

HughMark asks: I have 2 columns named customer (A1) and OR No. (B1). Under customer are names enumerated below them. […]

The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]

This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]

The picture above shows two lists. How do I highlight values in List 1 that are not in List 2? […]

### 27 Responses to “Identify missing numbers in a column”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

hi,

how can we extend the same to 8000 rows in a column,

creating array formula as you said is not working

Select cell B5

Copy / Paste array formula

Press and hold Ctrl + Shift

Press Enter

Release all keys

what should i do to continue ,

any alternative method to copy the array formula

expecting a reply at the earliest

anchal j vattakunnel,

Adjust cell range (bolded)

=SMALL(IF(ISERROR(MATCH($C$1+ROW(OFFSET($A$1, 0, 0, $C$2-$C$1+1))-1,

$A$2:$A$6, 0)), $C$1+ROW(OFFSET($A$1, 0, 0, $C$2-$C$1+1))-1), ROW(A1))Just wanted to say thanks. This worked on a huge range of data that I had. Really appreciate it!

hi

i have 65000 nos in a column how can in find the missing nos. the above formula cannot work.... please help me to rectify the problem. very urgent...

I couldn't get the formula to work either, by changing the range. Could you please help me also with out using VBA?

Thanks

Kenneth G,

How large is your range?

1-3660

The formula should work. Can you provide your formula? Did you create an array formula?

was not hitting ctrl+shift+enter. it works now!

Dear All,

I have serial numbers from 1 to 40,000 entry in excel. In-between serial numbers there some missing numbers. How can I findout what are the missing numbers from large serial numbers i.e. 1 to 40,000.

For e.g. there are serial numbers 1, 2, 4, 5, 6, 8, 9, 10 like wise i have 60,000numbers. Here missing numbers are 3, 7. How I will findout missing numbers 3 & 7 easily.

Kindly help me.

With advance thanks.

Regards,

Nihar

XLRI

abu and ravi,

I have added a vba solution to this post: Missing numbers (vba)

I want to find missing numbers starting from 80000001 to 80003200, how to find it by VBA code, Excel gets hang after entering VBA code.

Chetan Sonawane,

Yes, you are right. Try the new file Find-missing-values-version2.xlsm. Link above.

I want to split one single coloum of approximately 12000 values into several coloums so that I can take print of such numbers on pages, plz help me on this. how to do it ?

Chetan Sonawane,

Adjust cell range Sheet1!$A$1:$A$151.

Get the Excel *.xlsx file

Rearrange-data-from-a-column-to-multiple-columns.xlsx

Thank you Oscar very much, due to your help my work is getting easy. I tried spliting 12935 values in Excel using MS Office 2007, but the file works very slowly , Shall I install MS Office 2010 ?, will it work more faster ? I want your Advice.

I want to find missing value staring with alphabets like B00001 to B11221, how to find it by VBA code, please post new code.

Dear sir,

I wish to find missing nos. starting with alphabets like S0001 to S1122, Please send the code.

How to find missing nos which starts with B0001 or S-001

Dear Osacar Sir,

Please send me solution for finding missing nos starting with J000001 or S000001

Chetan Sonawane,

Create a new column and remove J and S from your lists.

If your list is in column A, cell B1:

=RIGHT(A1,LEN(A1)-1)*1

Copy formula downwards as far as needed.

Start macro and use it on column B.

Dear Sir,

Instead of formula can you help me with VBA code, If you can please modify VBA code for finding missing nos starting with J000001 or S000001

How i can put values of missing sequential numbers?

Number values

1 30

4 20

missing number 2 having value 10

missing number 3 having value 50

Hi Oscar,

I have tried the Array Formula, however, when i hit CTRL+Shift+Enter nothing happens. All the fields are highlighted but it does not provide any info. Any ideas?

Please help

how to use multi user in single macro program sir

Hi,

Kindly assist in finding the missing number in a specific range with dash along with the numbers.

Sample data.

385-234-4980

Hi sir,

SMALL(IF(ISERROR(MATCH(ROW(INDIRECT($E$2&":"&$E$3)),$B$3:$B$7,0)),ROW(INDIRECT($E$2&":"&$E$3))),ROWS($D$8:D8))

Same concept but with different construction..,,