Re: serial numbers how to find gaps

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

"Ron Coderre" <ronREMOVETHIScoderre@xxxxxxxxxxx> wrote in message
news:5EEA3629-E2AF-4018-B753-A9B160502C5F@xxxxxxxxxxxxxxxx
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


"DoronT" wrote:

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


.

Relevant Pages

  • Re: Query to find amissing number
    ... Here I need to write a query to find out that number 7 is missing in the ... given sequence. ... giving you a quick way to determine if any gaps exist. ...
    (comp.databases.sybase)
  • RE: Adding numbers to an incomplete numerical list
    ... If your sequence is in A1:A11000 ... beginning with 1, but containing gaps ... The missing numbers will appear at the top of the list....copy them to the ... "hana" wrote: ...
    (microsoft.public.excel.misc)
  • Re: uk.legal.moderated PING Palindrome & Steve Walker
    ... there are some gaps in this numbering - for example: ... What accounts for the gaps in the sequence? ... If this is uk.legal.moderated's numbering system and the ...
    (uk.legal)
  • Re: Sean Pitman: definitions wanted
    ... but are claiming and asserting without evidence that this ... gaps) is the only possible mechanism for evolution. ... *regardless* of the size of the sequence or the number of amino acids ...
    (talk.origins)
  • Re: Please help with no-gap autoincrement field
    ... My boss wants to have an autoincrement index with no gaps, ... I am new to ORACLE and based on my limited knowledge, ... autoincrement field with no gaps? ... as the source of the key rather than a sequence. ...
    (comp.databases.oracle.misc)