Re: 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@xxxxxxxxxxx> wrote in message
news:66EFA2B6-2E71-419B-B00A-9B0DE97C04AF@xxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:28706E9E-2624-4BD0-92BE-FB1A826148CF@xxxxxxxxxxxxxxxx
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.






.

Relevant Pages

  • Re: Find missing sequential numbers
    ... as missing, stopping at 65,656 of course. ... I prefer the amended "Biff formula". ... Since the sequence you're testing is 1:19 you just need to change the ... larger than the maximum referenced row number. ...
    (microsoft.public.excel.newusers)
  • Re: Find missing sequential numbers
    ... All good points, Biff ... as missing, stopping at 65,656 of course. ... Since the sequence you're testing is 1:19 you just need to change the ... larger than the maximum referenced row number. ...
    (microsoft.public.excel.newusers)
  • Re: Find missing sequential numbers
    ... but I want to make sure it has broader applications. ... as missing, stopping at 65,656 of course. ... I prefer the amended "Biff formula". ... Since the sequence you're testing is 1:19 you just need to change the ...
    (microsoft.public.excel.newusers)
  • Re: Find missing sequential numbers
    ... Since the sequence you're testing is 1:19 you just need to change the ROW ... Always eager to adopt a better solution, ... larger than the maximum referenced row number. ... I need to know 4 and 8 are missing. ...
    (microsoft.public.excel.newusers)
  • Re: Programmers unpaid overtime.
    ... "A polynomial is a mathematical expression involving ... And if terms are missing, or the exponents are out of sequence, the ... You all think I'm paranoid, ...
    (comp.programming)
Loading