Find missing sequential 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.