Quick, clean, and to the point

Formula challenge - flag out of sequence codes

The Problem

We have a list of alphanumeric codes. Each code consists of a single letter (A, B, C, etc.) followed by a 3-digit number. These codes should appear in alphabetical order, but sometimes they are out of sequence. We want to flag out-of-sequence codes.

Challenge #1

What formula in the  "Check" column will place an "x" next to a code that is out of sequence? In this challenge, we are only checking that the *numeric* portion of the code is out of sequence, not that the letter itself is out of sequence.

Out of sequence formula challenge #1

Challenge #2

How can the formula above be extended to check if "alpha" part of the code (A,B,C, etc.) is out of sequence? For example, we should flag a code that begins with "A" if it appears after a code that begins with "C" or "B".

Out of sequence formula challenge #2

Download the worksheet below and take the challenge!

Note: there are 2 sheets in the workbook, one for Challenge #1, one for Challenge #2.

Hint - This video shows some tips for how to solve a problem like this.


  1. All codes always contain four characters: 1 uppercase letter + 3 numbers.
  2. The number of codes per letter is random, but there should be no gaps in numeric values.
  3. It is only necessary to mark the first code with a letter out of sequence, not all subsequent codes.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.