Identify missing numbers in two columns based on a numerical range
Question:
I want to find missing numbers in two ranges combined? They are not adjacent.
Answer:
Array formula in cell B5:
How to create an array formula
 Select cell B5

Click in formula bar
 Copy and paste array formula to formula bar
 Press and hold Ctrl + Shift
 Press Enter
 Release all keys
To remove #NUM errors, Excel 2007 users can use this formula:
Named ranges
List1 (A2:A6)
List2 (E4:E6)
missing_list_start (B5)
What is named ranges?
Explaining array formula in cell B5
=SMALL(IF((COUNTIF(List1, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)))+COUNTIF(List2, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3))))=0, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)), ""), ROW(A1)))
Step 1  Create dynamic array with numbers from start to end
=SMALL(IF((COUNTIF(List1, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)))+COUNTIF(List2, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3))))=0, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)), ""), ROW(A1)))
ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3))
becomes
ROW(INDEX($A:$A, 1):INDEX($A:$A, 10))
becomes
ROW($A$1:INDEX($A:$A, 10))
becomes
ROW($A$1:$A$10)
and returns this array:
{1; 2; 3; 4; 5; 6; 7; 8; 9; 10}
Step 2  Find missing numbers
=SMALL(IF((COUNTIF(List1, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)))+COUNTIF(List2, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3))))=0, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)), ""), ROW(A1)))
(COUNTIF(List1, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)))+COUNTIF(List2, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3))))
becomes
(COUNTIF(List1, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10})+COUNTIF(List2, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}))
becomes
(COUNTIF({1;3;4;7;8}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10})+COUNTIF({1;2;5}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}))
{0;1;1;0;0;1;1;0;0} + {1;0;0;1;0;0;0;0;0}
and returns
{1;1;1;1;0;1;1;0;0}
Step 3  Convert boolean array into missing numbers
=SMALL(IF((COUNTIF(List1, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)))+COUNTIF(List2, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3))))=0, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)), ""), ROW(A1)))
IF((COUNTIF(List1, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)))+COUNTIF(List2, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3))))=0, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)), "")
becomes
IF(({1;1;1;1;0;1;1;0;0}, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)), "")
becomes
IF(({1;1;1;1;0;1;1;0;0}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}, "")
and returns
{"";"";"";"";6;"";"";9;10}
Step 4  Return the kth smallest number
=SMALL(IF((COUNTIF(List1, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)))+COUNTIF(List2, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3))))=0, ROW(INDEX($A:$A, $F$2):INDEX($A:$A, $F$3)), ""), ROW(A1)))
becomes
=SMALL({"";"";"";"";6;"";"";9;10}, ROW(A1))
becomes
=SMALL({"";"";"";"";6;"";"";9;10}, 1)
returns the number 6 in cell B5.
How to customize the formula to your excel spreadsheet
Change the named ranges.
Download excel sample file for this tutorial.
missingvaluesintwocolumns.xlsx
(Excel 2007 Workbook *.xlsx)
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 [โฆ]
What values are missing in List 1 that exists i List 2?
Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful [โฆ]
Identify missing numbers in a range
Question: How do I find missing numbers between 19 in a range? 1 3 4 5 6 7 8 8 [โฆ]
Insert blank rows for missing values
HughMark asks: I have 2 columns named customer (A1) and OR No. (B1). Under customer are names enumerated below them. [โฆ]
Find missing dates in a set of date ranges
The formula in cell B8, shown above, extracts dates not included in the date ranges, in other words, dates that [โฆ]
Identify missing three character alpha code numbers
This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: [โฆ]
Highlight missing values between to columns
The picture above shows two lists. How do I highlight values in List 1 that are not in List 2? [โฆ]
4 Responses to โIdentify missing numbers in two columns based on a numerical rangeโ
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
This is great. I need to do exactly the same thing, but with three character alpha codes instead of numbers. Can anyone help?
I tried to answer your question in this blog post: https://www.getdigitalhelp.com/2009/09/04/identifymissingthreecharacteralphacodenumbersinexcel/
Please help me out in Basic of Excel and VB formuals
i need to learn that including Array,which u have done in the top, how to start it and basic
rakesh,
I have added better array formula instructions to this post.