Missing sequence numbers

[rogerm]

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?

 

[stevebausch]

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]

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]

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.

 

[Aladin Akyurek]

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]

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