# 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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.

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.

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.

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

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

Replies
1
Views
347
Replies
7
Views
833
Replies
4
Views
183
Replies
3
Views
637
Replies
3
Views
147

1,221,127
Messages
6,158,101
Members
451,464
Latest member
Holden3

### 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