Thanks for indulging me in this formulaic pursuit.

Like yourself, I'm always looking to improve my skills and learn more!

This is a great place to do just that! (better than ANY book!)

Biff

"Ron Coderre" wrote in message
...
All good points, Biff

BTW...I wasn't looking for the perfect solution to all series scenarios. I
just wanted find a way to handle numeric series:
-containing numbers of almost any reasonable magnitude
-that did not necessarily start at 1
and could accommodate varying list sizes automatically

Thanks for indulging me in this formulaic pursuit.

(Oh...um...I have no idea where the 65,656 I posted came from...probably
the
number of still active brain cells in my head!)

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

XL2002, WinXP-Pro


"Biff" wrote:

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)),ROW S($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" wrote in message
...
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(CO UNTIF(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" wrote in message
...
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.