How to find missing numbers in series

Hello,

I have a column (OWNER ID, Col. B) with about 2500 rows of numbers, that are
supposed to be in succesion, incremented by 1, starting at 1. When I
highlighted the entire range, the last number in the series was 2546. But
the "COUNT" in the lower right corner of the screen shows only 2519. So I
now need to know where the missing numbers are, or better yet, where the
breaks are in the series.

How do I do this?

Thanks,

Phil

Hi Phill,

two ways

1. on an auxiliar column type =a2-a1 copy it down, use a conditional format
to highlight the results bigger than 1.

2. on an auxiliar column (D) ID 1 to 2519 = a1=d1 copy it down, copy again
paste especial values, find F

hth
regards from Brazil
Marcelo


"Phil" escreveu:

> Hello,
>
> I have a column (OWNER ID, Col. B) with about 2500 rows of numbers, that are
> supposed to be in succesion, incremented by 1, starting at 1. When I
> highlighted the entire range, the last number in the series was 2546. But
> the "COUNT" in the lower right corner of the screen shows only 2519. So I
> now need to know where the missing numbers are, or better yet, where the
> breaks are in the series.
>
> How do I do this?
>
> Thanks,
>
> Phil

you could do conditional formatting on the column and check to see if the
cell is = "" and color the cell, this should show blank cells (but not cells
with a space in them).

Also depending on what you want to do with those with missing data you could
just sort the column and all the ones with missing data should be grouped
seperte from the ones with numbers.

"Phil" wrote:

> Hello,
>
> I have a column (OWNER ID, Col. B) with about 2500 rows of numbers, that are
> supposed to be in succesion, incremented by 1, starting at 1. When I
> highlighted the entire range, the last number in the series was 2546. But
> the "COUNT" in the lower right corner of the screen shows only 2519. So I
> now need to know where the missing numbers are, or better yet, where the
> breaks are in the series.
>
> How do I do this?
>
> Thanks,
>
> Phil

Say your list starts in B2.
Enter this formula in C3, and copy down:

=B2+1=B3

This will give you a "False" in every row that is not in order.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Phil" <[email protected]> wrote in message
news:[email protected]...
Hello,

I have a column (OWNER ID, Col. B) with about 2500 rows of numbers, that are
supposed to be in succesion, incremented by 1, starting at 1. When I
highlighted the entire range, the last number in the series was 2546. But
the "COUNT" in the lower right corner of the screen shows only 2519. So I
now need to know where the missing numbers are, or better yet, where the
breaks are in the series.

How do I do this?

Thanks,

Phil

Hi Phil,

HTH and Marcello has already gave you the good answers to track the
error. Let me help you putting down the number in a series from 1 to
2500.

Click Menu EDIT>FILL>Series

Select Series in ROWS or COLUMNS then STEP VALUE as 1 and STOP VALUE as
2500. Click OK.

You will see the column or the row filled with the desired value
without any glitch.

Thanks,

Shail



Phil wrote:

> Hello,
>
> I have a column (OWNER ID, Col. B) with about 2500 rows of numbers, that are
> supposed to be in succesion, incremented by 1, starting at 1. When I
> highlighted the entire range, the last number in the series was 2546. But
> the "COUNT" in the lower right corner of the screen shows only 2519. So I
> now need to know where the missing numbers are, or better yet, where the
> breaks are in the series.
>
> How do I do this?
>
> Thanks,
>
> Phil

This might be helpful:

For a list number in B1:B2500 with some numbers missing

These formulas list all of the missing values

Enter this ARRAY FORMULA* in
C1:
=INDEX(ROW($B$1:INDEX(B:B,MAX(B:B))),SMALL(IF(COUNTIF($B$1:INDEX(B:B,MAX(B:B)),ROW($B$1:INDEX(B:B,MAX(B:B)))+MIN(B:B)-1)=0,ROW($B$1:INDEX(B:B,MAX(B:B)))+MIN(B:B)-1),ROWS($1:1))-MIN(B:B))+MIN(B:B)

Note_1: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy C1
Paste into C2 and down as far as you think you need

Note_2: The formulas take a little while to calculate (around 30 seconds on
my PC)


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Phil" wrote:

> Hello,
>
> I have a column (OWNER ID, Col. B) with about 2500 rows of numbers, that are
> supposed to be in succesion, incremented by 1, starting at 1. When I
> highlighted the entire range, the last number in the series was 2546. But
> the "COUNT" in the lower right corner of the screen shows only 2519. So I
> now need to know where the missing numbers are, or better yet, where the
> breaks are in the series.
>
> How do I do this?
>
> Thanks,
>
> Phil

Marcelo,

Thanks, the first solution worked fine. But I was unable to follow your
second example. Can you explain further what (D) ID 1 to 2519 = a1=d1 means?

"Marcelo" wrote:

> Hi Phill,
>
> two ways
>
> 1. on an auxiliar column type =a2-a1 copy it down, use a conditional format
> to highlight the results bigger than 1.
>
> 2. on an auxiliar column (D) ID 1 to 2519 = a1=d1 copy it down, copy again
> paste especial values, find F
>
> hth
> regards from Brazil
> Marcelo
>
>
> "Phil" escreveu:
>
> > Hello,
> >
> > I have a column (OWNER ID, Col. B) with about 2500 rows of numbers, that are
> > supposed to be in succesion, incremented by 1, starting at 1. When I
> > highlighted the entire range, the last number in the series was 2546. But
> > the "COUNT" in the lower right corner of the screen shows only 2519. So I
> > now need to know where the missing numbers are, or better yet, where the
> > breaks are in the series.
> >
> > How do I do this?
> >
> > Thanks,
> >
> > Phil

Hi RagDyeR,

Your solution was just what I needed. Very simple.

Thanks!

"RagDyeR" wrote:

> Say your list starts in B2.
> Enter this formula in C3, and copy down:
>
> =B2+1=B3
>
> This will give you a "False" in every row that is not in order.
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "Phil" <[email protected]> wrote in message
> news:[email protected]...
> Hello,
>
> I have a column (OWNER ID, Col. B) with about 2500 rows of numbers, that are
> supposed to be in succesion, incremented by 1, starting at 1. When I
> highlighted the entire range, the last number in the series was 2546. But
> the "COUNT" in the lower right corner of the screen shows only 2519. So I
> now need to know where the missing numbers are, or better yet, where the
> breaks are in the series.
>
> How do I do this?
>
> Thanks,
>
> Phil
>
>
>

Tim,

I don't have any empty cells at all. Every cell has some number in it.
Marcelo and RagDyeR gave me some good solutions, but thanks for your help
anyway.

"tim m" wrote:

> you could do conditional formatting on the column and check to see if the
> cell is = "" and color the cell, this should show blank cells (but not cells
> with a space in them).
>
> Also depending on what you want to do with those with missing data you could
> just sort the column and all the ones with missing data should be grouped
> seperte from the ones with numbers.
>
> "Phil" wrote:
>
> > Hello,
> >
> > I have a column (OWNER ID, Col. B) with about 2500 rows of numbers, that are
> > supposed to be in succesion, incremented by 1, starting at 1. When I
> > highlighted the entire range, the last number in the series was 2546. But
> > the "COUNT" in the lower right corner of the screen shows only 2519. So I
> > now need to know where the missing numbers are, or better yet, where the
> > breaks are in the series.
> >
> > How do I do this?
> >
> > Thanks,
> >
> > Phil

Shail,

I already know how to do the "Fill" command, but thanks for your input anyway.

"shail" wrote:

> Hi Phil,
>
> HTH and Marcello has already gave you the good answers to track the
> error. Let me help you putting down the number in a series from 1 to
> 2500.
>
> Click Menu EDIT>FILL>Series
>
> Select Series in ROWS or COLUMNS then STEP VALUE as 1 and STOP VALUE as
> 2500. Click OK.
>
> You will see the column or the row filled with the desired value
> without any glitch.
>
> Thanks,
>
> Shail
>
>
>
> Phil wrote:
>
> > Hello,
> >
> > I have a column (OWNER ID, Col. B) with about 2500 rows of numbers, that are
> > supposed to be in succesion, incremented by 1, starting at 1. When I
> > highlighted the entire range, the last number in the series was 2546. But
> > the "COUNT" in the lower right corner of the screen shows only 2519. So I
> > now need to know where the missing numbers are, or better yet, where the
> > breaks are in the series.
> >
> > How do I do this?
> >
> > Thanks,
> >
> > Phil
>
>

Ron,

I was able to use Marcelo's and RagDyeR's solutions to get what I wanted. I
have copied your solution to my Excel tricks folder, and if I need to get a
more accurate depiction of which numbers are missing I will surely need to
use this function.

"Ron Coderre" wrote:

> This might be helpful:
>
> For a list number in B1:B2500 with some numbers missing
>
> These formulas list all of the missing values
>
> Enter this ARRAY FORMULA* in
> C1:
> =INDEX(ROW($B$1:INDEX(B:B,MAX(B:B))),SMALL(IF(COUNTIF($B$1:INDEX(B:B,MAX(B:B)),ROW($B$1:INDEX(B:B,MAX(B:B)))+MIN(B:B)-1)=0,ROW($B$1:INDEX(B:B,MAX(B:B)))+MIN(B:B)-1),ROWS($1:1))-MIN(B:B))+MIN(B:B)
>
> Note_1: For array formulas, hold down [Ctrl] and [Shift] when you press
> [Enter], instead of just pressing [Enter].
>
> Copy C1
> Paste into C2 and down as far as you think you need
>
> Note_2: The formulas take a little while to calculate (around 30 seconds on
> my PC)
>
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "Phil" wrote:
>
> > Hello,
> >
> > I have a column (OWNER ID, Col. B) with about 2500 rows of numbers, that are
> > supposed to be in succesion, incremented by 1, starting at 1. When I
> > highlighted the entire range, the last number in the series was 2546. But
> > the "COUNT" in the lower right corner of the screen shows only 2519. So I
> > now need to know where the missing numbers are, or better yet, where the
> > breaks are in the series.
> >
> > How do I do this?
> >
> > Thanks,
> >
> > Phil

Appreciate the feed-back.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Phil" <[email protected]> wrote in message
news:[email protected]...
Hi RagDyeR,

Your solution was just what I needed. Very simple.

Thanks!

"RagDyeR" wrote:

> Say your list starts in B2.
> Enter this formula in C3, and copy down:
>
> =B2+1=B3
>
> This will give you a "False" in every row that is not in order.
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "Phil" <[email protected]> wrote in message
> news:[email protected]...
> Hello,
>
> I have a column (OWNER ID, Col. B) with about 2500 rows of numbers, that
are
> supposed to be in succesion, incremented by 1, starting at 1. When I
> highlighted the entire range, the last number in the series was 2546. But
> the "COUNT" in the lower right corner of the screen shows only 2519. So I
> now need to know where the missing numbers are, or better yet, where the
> breaks are in the series.
>
> How do I do this?
>
> Thanks,
>
> Phil
>
>
>