Exceljet

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.

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.
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course