serial numbers how to find gaps

[Guest]

Hello,

How can I find gaps in a column of serial numbers, gaps can be of one number
or more, gaps can appear several time in the same line. Example:

10010
10011
10012
10014
10015
10018

(gaps found are 10013, 10016, 10017)

I need to present those gaps in any possible way.


Thanks,

Doron

 

[Guest]

Hi Doron, one possible solution depending on the actual information you
require is as follows:

Say your column of serial numbers is colum A, on a blank column starting on
the row after the first row, type the following =IF(A2<>A1+1,"GAP","") then
drag this down to the bottom of your list.

this will not give you the actual missing numbers but will tell you where
there is a gap at a glance. you could substitute the text "GAP" for A1+1
which would give you the missing number however if 2 or more are missing in
sequence this wouldnt work correct.

Stu

 

[Roger Govier]

Hi

This recent posting by Biff of an array formula, creates a list of
missing numbers nicely bunched at the top of the column where the
formula is applied. For example, enter in B1
{=INDEX(ROW($1:$1000),
SMALL(IF(ISNA(MATCH(ROW($1:$1000),
A$1:A$1000,0)),ROW($1:$1000)),ROW(A1)))}

Array formulae have to be committed or edited with Ctrl+Shift+Enter
(CSE).
When you use CSE, Excel will add the curly braces { } around the
formula. Do not type them yourself.
Amend the ranges to suit, and once committed with CSE, copy down column
as far a required

 

[Guest]

Going by your posted example

With the series beginning in A1

Put this ARRAY FORMULA* in C1
=INDEX(ROW($A$1:INDEX(A:A,MAX(A:A))),SMALL(IF(COUNTIF($A$1:INDEX(A:A,MAX(A:A)),ROW($A$1:INDEX(A:A,MAX(A:A)))+MIN(A:A)-1)=0,ROW($A$1:INDEX(A:A,MAX(A:A)))+MIN(A:A)-1),ROWS($1:1))-MIN(A:A))+MIN(A:A)

*Note_1: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Note_2: There are NO spaces in that formula.

Copy C1 and paste into C2 and down as far as you need.

That formula automatically starts listing values, beginning with the first
missing value after the smallest value in the list and continuing listing
misisng values up to the largest value in the list. It doesn't matter if the
list is not in order


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Biff]

After getting into a discussion of this topic with Epinn, we've refined the
formula. The call to Index is not needed. Array entered:

=SMALL(IF(ISNA(MATCH(ROW($10010:$10018),A$1:A$6,0)),ROW($10010:$10018)),ROW(A1))

Note that this method is slow on large sequences but if it's a one-time
operation where you can convert the formulas to constants afterwards, it's
pretty simple and straightforward.

Biff

 

[Biff]

This assumes the start of the sequence is listed. What if it's one of the
missing values? Also, it doesn't stop. There's no defined end of sequence.

Biff

Going by your posted example

With the series beginning in A1

Put this ARRAY FORMULA* in C1
=INDEX(ROW($A$1:INDEX(A:A,MAX(A:A))),SMALL(IF(COUNTIF($A$1:INDEX(A:A,MAX(A:A)),ROW($A$1:INDEX(A:A,MAX(A:A)))+MIN(A:A)-1)=0,ROW($A$1:INDEX(A:A,MAX(A:A)))+MIN(A:A)-1),ROWS($1:1))-MIN(A:A))+MIN(A:A)

*Note_1: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Note_2: There are NO spaces in that formula.

Copy C1 and paste into C2 and down as far as you need.

That formula automatically starts listing values, beginning with the first
missing value after the smallest value in the list and continuing listing
misisng values up to the largest value in the list. It doesn't matter if
the
list is not in order


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Hello,

How can I find gaps in a column of serial numbers, gaps can be of one
number
or more, gaps can appear several time in the same line. Example:

10010
10011
10012
10014
10015
10018

(gaps found are 10013, 10016, 10017)

I need to present those gaps in any possible way.


Thanks,

Doron