Summary

We have a simple list of 4-digit alphanumeric codes like A001, A002, A003, etc. What formula can we use to mark codes that are "out of sequence" with previous entries? This problem includes two separate challenges.

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.

Assumptions

  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.