A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How to find missing numbers in series



 
 
Thread Tools Display Modes
  #1  
Old July 25th 06, 03:44 PM posted to microsoft.public.excel.worksheet.functions
phil
external usenet poster
 
Posts: 201
Default 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
Ads
  #2  
Old July 25th 06, 04:23 PM posted to microsoft.public.excel.worksheet.functions
Marcelo
external usenet poster
 
Posts: 1,047
Default How to find missing numbers in series

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

  #3  
Old July 25th 06, 04:29 PM posted to microsoft.public.excel.worksheet.functions
tim m
external usenet poster
 
Posts: 430
Default How to find missing numbers in series

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

  #4  
Old July 25th 06, 04:39 PM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,574
Default How to find missing numbers in series

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" > wrote in message
...
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


  #5  
Old July 25th 06, 04:49 PM posted to microsoft.public.excel.worksheet.functions
shail
external usenet poster
 
Posts: 195
Default How to find missing numbers in series

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


  #6  
Old July 26th 06, 04:38 PM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,574
Default How to find missing numbers in series

Appreciate the feed-back.
--

Regards,

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

"Phil" > wrote in message
...
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" > wrote in message
> ...
> 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
>
>
>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
find sum in list of of numbers Jim Thomlinson New Users to Excel 5 January 4th 06 07:07 PM
Find similar numbers from two columns of numbers Dennis Andrews Excel Discussion (Misc queries) 1 November 30th 05 07:54 AM
find a sum from a list of numbers Keven Excel Worksheet Functions 1 July 21st 05 07:39 PM
Find the combination of numbers that when added equal a reqired total?? Handsy11 Excel Worksheet Functions 5 July 12th 05 04:55 PM
Find Median of Positive numbers only in Range MichaelC Excel Worksheet Functions 4 June 24th 05 03:06 AM


All times are GMT +1. The time now is 06:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.