>I hope you understand that I like your approach,
>but I want to make sure it has broader applications.

Yeah, it is limited as I stated!

Here's how *I* would approach your example since 198 is constant:

=INDEX(ROW($766:$774),SMALL(IF(--RIGHT(A$1:A$9,3)<>ROW($766:$774),ROW(A$1:A$9)),ROWS($1:1)))

returns:

768
772

The subject of this thread leads to all kinds of "ugliness"!

What if the invoice numbers were preceded by the year:

2006-198774

Or, were the last 4 digits of the string:

1987742006

Or contained some alpha characters:

198A999-2006
198A1000-2006

I think creating a "generic" solution for every possible situation would be
near impossible so any solution has to be crafted for the specific
situation. At least, that's how I approach things. I believe that at some
point "robustness", which some consider to be the "holy grail", leads to
overkill!

Biff

"Ron Coderre" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
news:33838C3F-7357-41AC-8D1A-3EF5BC37D2B8@microsoft.com...
> Biff:
>
> I hope you understand that I like your approach, but I want to make sure
> it
> has broader applications.
>
> Example:
> A1:A9 contains invoice numbers:
> 198766
> 198774
> 198767
> 198773
> 198769
> 198771
> 198769
> 198771
> 198770
>
> Which ones are missing?
>
> As it stands, your original formula would try to list from 1 through
> 198765
> as missing, stopping at 65,656 of course.
>
> After more play, I came up with this array formula:
> B1:
> =INDEX(ROW($A$1:INDEX(A:A,COUNT(A:A))),SMALL(IF(COUNTIF(A$1:A$9,ROW($1:$9)+MIN(A:A)-1)=0,ROW($1:$9)+MIN(A:A)-1),ROWS($1:1))-MIN(A:A))+MIN(A:A)
> (copied down)
>
> It returned:
> 198768
> 198772
>
> Note: I also had to tweak my formula to make it work. It ended up a
> few
> characters shorter, but I'd much rather have the missing values list in
> ascending order. Consequently, I prefer the amended "Biff formula".
>
> Your thoughts?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Biff" wrote:
>
>> Hi Ron!
>>
>> It works for me.
>>
>> Since the sequence you're testing is 1:19 you just need to change the
>> ROW()
>> range to match that sequence:
>>
>> =INDEX(ROW($1:$19),SMALL(IF(COUNTIF(A$1:A$9,ROW($1:$19))=0,ROW($1:$19)),ROWS($1:1)))
>>
>> Biff
>>
>> "Ron Coderre" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
>> news:66EFA2B6-2E71-419B-B00A-9B0DE97C04AF@microsoft.com...
>> > 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(COUNTIF(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" <DTTODGG@discussions.microsoft.com> wrote in message
>> >> news:28706E9E-2624-4BD0-92BE-FB1A826148CF@microsoft.com...
>> >> > 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.
>> >>
>> >>
>> >>
>>
>>
>>