Find missing sequential numbers

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.
> >>
> >>
> >>
>
>
>

Latest in a series of final adjustments :\

To make my last "Biff Formula" reactive to the number of items in Col_A I
just changed it to this:

=INDEX(ROW($A$1:INDEX(A:A,COUNT(A:A))),SMALL(IF(COUNTIF($A$1:INDEX(A:A,COUNT(A:A)),ROW($A$1:INDEX(A:A,COUNT(A:A)))+MIN(A:A)-1)=0,ROW($A$1:INDEX(A:A,COUNT(A:A)))+MIN(A:A)-1),ROWS($1:1))-MIN(A:A))+MIN(A:A)

Now, I've noticed another problem, though:
It can fail if there are blank cells in the Col_A range.

My amended array formula seems to handle that situation:
=LARGE(ISNA(MATCH(ROW($A$1:INDEX(A:A,MAX(A:A)-MIN(A:A)))+MIN(A:A)-1,$A$1:INDEX(A:A,MAX(A:A)-MIN(A:A)),0))*(ROW($A$1:INDEX(A:A,MAX(A:A)-MIN(A:A)))+MIN(A:A)-1),ROW())

BUT...it lists the missing values in descending order. I'd rather get the
numbers in ascending order.

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

XL2002, WinXP-Pro


"Ron Coderre" wrote:

> 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.
> > >>
> > >>
> > >>
> >
> >
> >

>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.
>> >>
>> >>
>> >>
>>
>>
>>

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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

>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.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>