## 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.

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 […]

### 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.

**Contact Oscar**

You can contact me through this contact form

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))