Identify missing numbers in list

[Lj]

Is there a formula to identify missing numbers in a list of consecutive
numbers?

 

[Max]

Assume numbers are listed in A1:A3, eg: 1,3,6
Assume the range of numbers to check is 10 numbers, ie: 1-10
In B1: =IF(ISNUMBER(MATCH(ROWS($1:1),A:A,0)),"",ROWS($1:1))
In C1: =IF(ROWS($1:1)>COUNT(B:B),"",INDEX(B:B,SMALL(B:B,ROWS($1:1))))
Copy B1:C1 down the extent, ie by 10 rows to C10. Col C will return the
required results, ie the missing numbers within the range, all neatly bunched
at the top. Adapt to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik

 

[T. Valko]

One way...

Assumptions:

A1:A50 = range of numbers
The sequence to check against is 1 to 100

Try this array formula** :

=SMALL(IF(ISNA(MATCH(ROW(A$1:A$100),A$1:A$50,0)),ROW(A$1:A$100)),ROW(A1))

Copy down until you get #NUM! errors meaning all missing numbers have been
listed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

This method is slow to calculate on very large sequences.

 

[Infinitogool]

Hi Lj

If so, you could try this, array-entered,
ie press CTRL+SHIFT+ENTER to confirm the formula:

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT(MIN(x)&":"&MAX(x))),x,0))
,ROW(INDIRECT(MIN(x)&":"&MAX(x))),""),ROW(INDIRECT("1:"&(MAX(x)-MIN(x)-COUNT(x)+1))))

where x is a rang of dates

http://tinyurl.com/5nv7vp

Regards,
Pedro J.

 

[Lj]

Thanx Max, quick reply, only thing.....
Column C remains blank.
Let me elaborate, this is a list of cheques. The ist chq.# is 929 the last
is 1667. Do I substitute ($929:1) for ($1:1) or($1:929). That may be where my
problem lies.

 

[Max]

Try it this way ..

Source numbers (assumed real numbers) in A1 down

In B1:
=IF(ISNUMBER(MATCH(ROWS($1:1)+928,A:A,0)),"",ROWS($1:1)+928)
(Add 928 to ROWS($1:1) so that it begins with 929 - your 1st number)

In C1:
=IF(ROWS($1:1)>COUNT(B:B),"",SMALL(B:B,ROWS($1:1)))
Copy B1:C1 down by 739* rows to C739
*the range: 929 to 1667 contains 1667-929+1 = 739 numbers
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik