# Missing sequence numbers

#### rogerm

##### Board Regular
I have a list of serial numbers in column A which should increment by 1. I would like to find a way of showing if there are any numbers missing in the sequence. Is this possible?

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### stevebausch

##### Well-known Member
Sure, number of ways.

I am ignorant of cell formulas, by and large. Any answer I give is usually a VBA-oriented answer. Just so you know...

You could have a macro step though row (using .offset(1,0).select).

Test the current value against the previous value; if more than a difference of one, then pop up your msgbox, etc.

#### rogerm

##### Board Regular
Vba would be OK.
How would I make it step through Column A and return a value in colun B. of eg "error" or the value in A +1 to suggest the next sequence number missing.

#### Jim North

##### MrExcel MVP
rogerm

You could you CONDITIONAL FORMATTING to highlight when a number is skipped using the following formula (assumes first number is in A1):

=NOT(A2=A1+1)

For example, you could change every occurrence where a number is skipped to red.

##### MrExcel MVP
What follows will determine how many numbers are missing:

=(MAX(A:A)-MIN(A:A)+1)-MATCH(BigNum,A:A)-(CELL("row",A1)-1)

A1 is the cell where the sequence start. Adjust to suit. BigNum is a name you can define as follows:

(1.) Activate Insert|Name|Define.
(2.) Enter BigNum as name in the Names in Workbook box.
(3.) Enter in the Refers to box:

9.99999999999999E+307

(4.) Click OK.

Use conditional formatting, as Jim suggests, to determine which numbers are missing their predecessors, with as formula:

=NOT(A2=A1+1)

or

=A2<>A1+1
This message was edited by Aladin Akyurek on 2002-09-25 09:09

#### rogerm

##### Board Regular
Many thanks for all the useful suggestions.
the highlighting and count of missing numbers will provide the information I need.

Replies
5
Views
100
Replies
11
Views
223
Replies
13
Views
236
Replies
4
Views
226
Replies
2
Views
42

1,147,818
Messages
5,743,382
Members
423,792
Latest member
travisds

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back