Find missing sequential numbers

[DTTODGG]

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.

 

[JE McGimpsey]

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.

 

[Ron Coderre]

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]

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.

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

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.

 

[DTTODGG]

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]

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.