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.
Get 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.
Three character alpha code.xlsm
Missing values category
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]
Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]
Excel categories
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))