>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" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
news:83E3D92C-F6EA-4321-9599-BAAB2111EEAE@microsoft.com...
> 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)),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.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>