Find missing sequential numbers

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.

One way:

select B2:Bx. Choose Format/Conditional Formatting...

CF1: Formula is =(B2-B1)>1
Format1: <pattern>/<some color>

or, without sorting, select column B (with B1 active):

CF1: Formula is =AND(B1>MIN(B:B),COUNTIF(B:B,B1-1)=0)

Both CF's will activate if there are missing numbers before them.


In article <28706E9E-2624-4BD0-92BE-FB1A826148CF@microsoft.com>,
DTTODGG <DTTODGG@discussions.microsoft.com> wrote:

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

Thank you , JE.

Both examples work wonderfully. Can you explain the 2 CF1? I'm really trying
not only to get my tasks done (with your help) but actually learn about excel
as I go.

"JE McGimpsey" wrote:

> One way:
>
> select B2:Bx. Choose Format/Conditional Formatting...
>
> CF1: Formula is =(B2-B1)>1
> Format1: <pattern>/<some color>
>
> or, without sorting, select column B (with B1 active):
>
> CF1: Formula is =AND(B1>MIN(B:B),COUNTIF(B:B,B1-1)=0)
>
> Both CF's will activate if there are missing numbers before them.
>
>
> In article <28706E9E-2624-4BD0-92BE-FB1A826148CF@microsoft.com>,
> DTTODGG <DTTODGG@discussions.microsoft.com> wrote:
>
> > 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?
>

The 2nd CF just checks that, for all values greater than the smallest
one, there's at least one number equal to that value - 1. So if there's
a 4, it checks that there's a 3. If there's not (and as long as 4 is not
the minimum number), it activates the conditional format.


In article <2ACB1CC6-538C-4C70-B3F1-59A4068F16D7@microsoft.com>,
DTTODGG <DTTODGG@discussions.microsoft.com> wrote:

> Both examples work wonderfully. Can you explain the 2 CF1? I'm really trying
> not only to get my tasks done (with your help) but actually learn about excel
> as I go.

Try something like this:

With your list of values in Cells A1:A10

B1:
=LARGE(ISNA(MATCH(ROW($A$1:INDEX(A:A,MAX(A:A))),$A$1:$A$8,0))*ROW($A$1:INDEX(A:A,MAX(A:A))),ROW())

Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

Copy B1 and paste it into B2 and down as far as you need.

Does that give you something to work with?

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

XL2002, WinXP-Pro


"DTTODGG" wrote:

> 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 Ron;

It works, but I have no idea how - I need a tutorial on common syntax and
examples used in excel (and access).

I would really like to learn how to come up with these on my own someday.

"Ron Coderre" wrote:

> Try something like this:
>
> With your list of values in Cells A1:A10
>
> B1:
> =LARGE(ISNA(MATCH(ROW($A$1:INDEX(A:A,MAX(A:A))),$A$1:$A$8,0))*ROW($A$1:INDEX(A:A,MAX(A:A))),ROW())
>
> Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
> press [Enter].
>
> Copy B1 and paste it into B2 and down as far as you need.
>
> Does that give you something to work with?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "DTTODGG" wrote:
>
> > 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.

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.

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

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

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