Missing sequence numbers

rogerm

Board Regular
Joined
May 12, 2002
Messages
53
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
Joined
May 11, 2002
Messages
810
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
Joined
May 12, 2002
Messages
53
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
Joined
Jun 20, 2002
Messages
791
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
May 12, 2002
Messages
53
Many thanks for all the useful suggestions.
the highlighting and count of missing numbers will provide the information I need.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top