A list of Consecutive Integers, can I search for missing integers

CM wrote...
>If I have a column of sorted consecutive Integers, can I search that column
>and find cells that might be missing a consecutive Integer? We have a list
>of customer numbers and if we don't have a number in the sorted sequential
>order we want to be able to know that. Any help?

If you have a column of sorted integers in a range named LST in which
all the integers should be consecutive, your first test should be
whether there are any duplicates. That can be done using

=SUMPRODUCT((LST<>"")/(COUNTIF(LST,LST)+(LST="")))=COUNT(LST)

If this returns TRUE, then all numbers in the list are distinct.

Next, check that they're all integers.

=SUMPRODUCT(--(MOD(LST,1)=0))=COUNT(LST)

Once these two tests have been passed, it's simple to check if they're
consecutive.

=MAX(LST)-MIN(LST)=COUNT(LST)-1

If they're not, then the index within LST of the k_th nonconsecutive
integer is given by the array formula

=SMALL(IF(OFFSET(LST,0,0,COUNT(LST)-1,1)<OFFSET(LST,1,0,COUNT(LST)-1,1)-1,
ROW(INDIRECT("2:"&ROWS(LST)))),k)

Why not insert a helper column and calculate the difference between the two
values.

"CM" wrote:

> If I have a column of sorted consecutive Integers, can I search that column
> and find cells that might be missing a consecutive Integer? We have a list
> of customer numbers and if we don't have a number in the sorted sequential
> order we want to be able to know that. Any help?

This works with a starting value of 1000 in cell A2, so that the integer
MINUS 998 = the Row#, and returns the row of the first nonconsecutive integer:

=MAX(--(A2:A28-998=ROW(A2:A28))*ROW(A2:A28))

"CM" wrote:

> If I have a column of sorted consecutive Integers, can I search that column
> and find cells that might be missing a consecutive Integer? We have a list
> of customer numbers and if we don't have a number in the sorted sequential
> order we want to be able to know that. Any help?

And it needs to be entered as an array formula with

Ctrl-Shift-Enter


"Duke Carey" wrote:

> This works with a starting value of 1000 in cell A2, so that the integer
> MINUS 998 = the Row#, and returns the row of the first nonconsecutive integer:
>
> =MAX(--(A2:A28-998=ROW(A2:A28))*ROW(A2:A28))
>
> "CM" wrote:
>
> > If I have a column of sorted consecutive Integers, can I search that column
> > and find cells that might be missing a consecutive Integer? We have a list
> > of customer numbers and if we don't have a number in the sorted sequential
> > order we want to be able to know that. Any help?

CM wrote...
>If I have a column of sorted consecutive Integers, can I search that column
>and find cells that might be missing a consecutive Integer? We have a list
>of customer numbers and if we don't have a number in the sorted sequential
>order we want to be able to know that. Any help?

If you have a column of sorted integers in a range named LST in which
all the integers should be consecutive, your first test should be
whether there are any duplicates. That can be done using

=SUMPRODUCT((LST<>"")/(COUNTIF(LST,LST)+(LST="")))=COUNT(LST)

If this returns TRUE, then all numbers in the list are distinct.

Next, check that they're all integers.

=SUMPRODUCT(--(MOD(LST,1)=0))=COUNT(LST)

Once these two tests have been passed, it's simple to check if they're
consecutive.

=MAX(LST)-MIN(LST)=COUNT(LST)-1

If they're not, then the index within LST of the k_th nonconsecutive
integer is given by the array formula

=SMALL(IF(OFFSET(LST,0,0,COUNT(LST)-1,1)<OFFSET(LST,1,0,COUNT(LST)-1,1)-1,
ROW(INDIRECT("2:"&ROWS(LST)))),k)

Why not insert a helper column and calculate the difference between the two
values.

"CM" wrote:

> If I have a column of sorted consecutive Integers, can I search that column
> and find cells that might be missing a consecutive Integer? We have a list
> of customer numbers and if we don't have a number in the sorted sequential
> order we want to be able to know that. Any help?

This works with a starting value of 1000 in cell A2, so that the integer
MINUS 998 = the Row#, and returns the row of the first nonconsecutive integer:

=MAX(--(A2:A28-998=ROW(A2:A28))*ROW(A2:A28))

"CM" wrote:

> If I have a column of sorted consecutive Integers, can I search that column
> and find cells that might be missing a consecutive Integer? We have a list
> of customer numbers and if we don't have a number in the sorted sequential
> order we want to be able to know that. Any help?

And it needs to be entered as an array formula with

Ctrl-Shift-Enter


"Duke Carey" wrote:

> This works with a starting value of 1000 in cell A2, so that the integer
> MINUS 998 = the Row#, and returns the row of the first nonconsecutive integer:
>
> =MAX(--(A2:A28-998=ROW(A2:A28))*ROW(A2:A28))
>
> "CM" wrote:
>
> > If I have a column of sorted consecutive Integers, can I search that column
> > and find cells that might be missing a consecutive Integer? We have a list
> > of customer numbers and if we don't have a number in the sorted sequential
> > order we want to be able to know that. Any help?

If I have a column of sorted consecutive Integers, can I search that column
and find cells that might be missing a consecutive Integer? We have a list
of customer numbers and if we don't have a number in the sorted sequential
order we want to be able to know that. Any help?

CM wrote...
>If I have a column of sorted consecutive Integers, can I search that column
>and find cells that might be missing a consecutive Integer? We have a list
>of customer numbers and if we don't have a number in the sorted sequential
>order we want to be able to know that. Any help?

If you have a column of sorted integers in a range named LST in which
all the integers should be consecutive, your first test should be
whether there are any duplicates. That can be done using

=SUMPRODUCT((LST<>"")/(COUNTIF(LST,LST)+(LST="")))=COUNT(LST)

If this returns TRUE, then all numbers in the list are distinct.

Next, check that they're all integers.

=SUMPRODUCT(--(MOD(LST,1)=0))=COUNT(LST)

Once these two tests have been passed, it's simple to check if they're
consecutive.

=MAX(LST)-MIN(LST)=COUNT(LST)-1

If they're not, then the index within LST of the k_th nonconsecutive
integer is given by the array formula

=SMALL(IF(OFFSET(LST,0,0,COUNT(LST)-1,1)<OFFSET(LST,1,0,COUNT(LST)-1,1)-1,
ROW(INDIRECT("2:"&ROWS(LST)))),k)

Why not insert a helper column and calculate the difference between the two
values.

"CM" wrote:

> If I have a column of sorted consecutive Integers, can I search that column
> and find cells that might be missing a consecutive Integer? We have a list
> of customer numbers and if we don't have a number in the sorted sequential
> order we want to be able to know that. Any help?

This works with a starting value of 1000 in cell A2, so that the integer
MINUS 998 = the Row#, and returns the row of the first nonconsecutive integer:

=MAX(--(A2:A28-998=ROW(A2:A28))*ROW(A2:A28))

"CM" wrote:

> If I have a column of sorted consecutive Integers, can I search that column
> and find cells that might be missing a consecutive Integer? We have a list
> of customer numbers and if we don't have a number in the sorted sequential
> order we want to be able to know that. Any help?

And it needs to be entered as an array formula with

Ctrl-Shift-Enter


"Duke Carey" wrote:

> This works with a starting value of 1000 in cell A2, so that the integer
> MINUS 998 = the Row#, and returns the row of the first nonconsecutive integer:
>
> =MAX(--(A2:A28-998=ROW(A2:A28))*ROW(A2:A28))
>
> "CM" wrote:
>
> > If I have a column of sorted consecutive Integers, can I search that column
> > and find cells that might be missing a consecutive Integer? We have a list
> > of customer numbers and if we don't have a number in the sorted sequential
> > order we want to be able to know that. Any help?