# Thread: Missing sequence numbers (Excel 2000/XP)

1. ## Missing sequence numbers (Excel 2000/XP)

I have a spreadsheet that has invoice numbers in column A. They run in numerical order but there are some missing. Is there any way to have Excel look at this and tell me where the sequence breaks at each missing number?

2. ## Re: Missing sequence numbers (Excel 2000/XP)

A very simple approach would be to sort the invoices in ascending order, insert a new column B, and in column B, second row of invoice numbers (I'll assume it's row 3) enter

=IF(A3<>A2+1,"SEQUENCE BREAK","")

copy it down and look for the break messages. There may be more complex array formula approaches to count the number of breaks, I'll look around.

3. ## Re: Missing sequence numbers (Excel 2000/XP)

How about the following, where my list is in A6 to A10 (adjust accordingly for yours)

{ =SUM(IF(A6:A10<>A5:A9+1,1,0))-1 }

as an array formula entered in, say, cell B6.

You type the formula starting from = but don't enter the braces. Using CTRL+SHIFT+enter tells Excel this is an Array formula and it puts in the braces in the formula bar.

1. list can't start in row 1 (assuming it's vertical) since the right side of the comparison looks at the cell before the first cell in the list.
2. The -1 at the end is based on the assumption that the first cell (A6 in my case) won't compare to the cell above it so I have to adjust for that.

Everything I know about Array Formulas is due to Bob U.

Fred

4. ## Re: Missing sequence numbers (Excel 2000/XP)

My previous post would just give you a count of the gaps. If there are none, the count is 0 of course.

If you want to have the adjacent cell say whether there's a gap between the cell to the left and the one above, you can use what John suggested or you can use the array formula

{ =IF(A7:A10<>A6:A9+1,"gap","no gap") }
CTRL+SHIFT+entered with A7:A10 selected

Here I purposely start with the second cell in the list to avoid the problem of the first cell mentioned in the previous post.

This is pretty much equivalent to John's approach.

With either of these, you could then get a count of the gaps, using my array formula from earlier or something like
=countif(B7:B10,"gap")

You might even want to do a conditional format on the entries in Col B to shade the cells with the word "gap" so they stand out.

Fred

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•