Biff:

Always eager to adopt a better solution, I experimented with the formula you
posted and I ran into an issue.

I entered the below series in cells A1:A9
5
6
9
10
15
16
17
18
19

...and the formula only identifed missing items: 1, 2, 3, 4, 7 and 8,
ignoring 11, 12, 13 and 14.

Evidently, it only works as long as the maximum number in the sequence isn't
larger than the maximum referenced row number.

This amended version got it back on track:
=INDEX(ROW($A$1:INDEX(A:A,MAX(A:A))),SMALL(IF(COUN TIF(A$1:A$9,ROW($A$1:INDEX(A:A,MAX(A:A))))=0,ROW($ A$1:INDEX(A:A,MAX(A:A)))),ROWS($1:1)))

***********
Regards,
Ron

XL2002, WinXP-Pro


"Biff" wrote:

Here's another one: (array entered)

=INDEX(ROW($1:$9),SMALL(IF(COUNTIF(A$1:A$7,ROW($1: $9))=0,ROW($1:$9)),ROWS($1:1)))

Copy down until you #NUM!.

This one is limited to number sequences from 1 to 65536. (in Excel 12 that
will jump up to 1048576 !)

Biff

"DTTODGG" wrote in message
...
Hello, I'm looking for a way to quickly find what numbers are missing in
column B. I can sort them ascending, but how do I find if there are
missing
numbers?
1
2
3
5
6
7
9
I need to know 4 and 8 are missing.
Thank you.