Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 EDITFILLSeries 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find sum in list of of numbers | New Users to Excel | |||
Find similar numbers from two columns of numbers | Excel Discussion (Misc queries) | |||
find a sum from a list of numbers | Excel Worksheet Functions | |||
Find the combination of numbers that when added equal a reqired total?? | Excel Worksheet Functions | |||
Find Median of Positive numbers only in Range | Excel Worksheet Functions |