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: I need to do exactly the same thing, but with three character alpha codes instead of numbers. Can anyone help?
There are 26*26*26 = 17,576 different alpha codes beginning with AAA and ending with ZZZ. Column F contains all 17,576 alpha codes.
The formula in cell D3 compares the values in column B with the values in column F and extracts only those who are missing in column B.
Array formula in cell D3:
Explaining array formula in cell D3
Step 1 - Identify missing values
The COUNTIF function counts values based on a condition or criteria, in this case, it counts values in $F$3:$F$17578 based on $B$3:$B$11.
A 0 (zero) indicates a missing value.
COUNTIF($B$3:$B$11, $F$3:$F$17578)=0
becomes
{0;1;0;1;1;0;0;0;0;0;0;0 ... 0}=0
and returns
{TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}
Step 2 - Replace TRUE with corresponding row number
The IF function returns the corresponding row number if boolean value is TRUE. FALSE returns "" (nothing).
IF(COUNTIF($B$3:$B$11, $F$3:$F$17578)=0, MATCH(ROW($F$3:$F$17578), ROW($F$3:$F$17578)), "")
becomes
IF({TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}, MATCH(ROW($F$3:$F$17578), ROW($F$3:$F$17578)), "")
and returns
{"";2;"";4;5;"";"";"";"";"";"" ... ""}
Step 3 - Extract row number
The SMALL function returns the k-th smallest number in a cell range or array. SMALL(array, k)
SMALL(IF(COUNTIF($B$3:$B$11, $F$3:$F$17578)=0, MATCH(ROW($F$3:$F$17578), ROW($F$3:$F$17578)), ""), ROWS($A$1:A1))
becomes
SMALL({"";2;"";4;5;"";"";"";"";"";"" ... ""}, ROWS($A$1:A1))
The ROWS function returns the number of rows in a cell reference, in this case the cell reference expands as the cell is copied to cells below.
SMALL({"";2;"";4;5;"";"";"";"";"";"" ... ""}, ROWS($A$1:A1))
becomes
SMALL({"";2;"";4;5;"";"";"";"";"";"" ... ""}, 1)
and returns 2.
Step 4 - Return value
The INDEX function returns a value based on a row and column number. The column number is not needed here.
INDEX($F$3:$F$17578, SMALL(IF(COUNTIF($B$3:$B$11, $F$3:$F$17578)=0, MATCH(ROW($F$3:$F$17578), ROW($F$3:$F$17578)), ""), ROWS($A$1:A1)))
becomes
INDEX($F$3:$F$17578, 2)
and returns "AAB" in cell D3.
Download excel *.xlsm file
I made all the alpha codes using a UDF, you don't need the UDF to extract missing alpha codes so you can disable macros for this workbook if you like.
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 1-9 in a range? 1 3 4 5 6 7 8 8 […]
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 […]
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 […]
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? […]
One Response to “Identify missing three character alpha code numbers”
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.
Why you use List1 and List2?
Alternative (array formula) :
=SMALL(IF(COUNTIF($A$2:$B$16, ROW($65:$90))=0, ROW($65:$90), ""),ROW(A1))