Excel: Find Missing Number In Sequence

Here's how to use MS Excel to find a missing number in a sequence.

The assumption is you have a column of numbers, say, Delivery Receipt Numbers, and that column is sorted in ascending order (from lowest to highest).

This Excel tip will make use of Conditional Formatting, so please hide your reading glasses because I won't ask you to read down a long column of numbers looking for the missing or out of sequence figures.

1. Highlight the second number all the way down to the last. So if your numbers are in cells A1 down to A100, please highlight cells A2 to A100.

2. Click on Format - Conditional Formatting

3. Click on the "Cell Value Is" dropdown list, then click on "Formula Is"

4. In the box to the right, type +(A2-A1)>1

5. Click on the "Format" button, the "Patterns" tab, and the background color that you like.

6. Click on the "OK" button.

7. Click on the "Add »" button.

8. Click on the "Cell Value Is" dropdown list, then click on "Formula Is"

9. In the box to the right, type +(A2-A3)<-1

10. Click on the "Format" button, the "Patterns" tab, and the background color that you like.

11. Click on the "OK" button. Click on the Conditional Formatting dialog box's "OK" button.

Test your column of numbers by typing in a number that is not in sequence. You'll notice that the numbers before and after the missing number will have a colored background. And that's how you can easily spot the missing Official Receipt or Delivery Receipt number in a sequence of consecutive numbers using your Excel spreadsheet.

Source: http://youtube.com/watch?v=jODw8EmSw54

[ First posted on 07/11/2007 by Manuel Viloria ]