Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding missing values in a column
I'm looking for a way to automaticaly find missing values in a column of
numbers. Here's an example (all values are in the same column): 50 51 52 54 55 Value 53 is missing from the column (which should be increasing by 1 every row). Is there a way to automatically detect that there is a gap between 2 values? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding missing values in a column
With your list of contiguous source numbers beginning anywhere in Col_A
Example: 50 51 52 54 55 This regular formula returns the 1st missing value B1: =SMALL(INDEX((COUNTIF(A:A,ROW(INDIRECT(MIN(A:A)&": "&MAX(A:A))))=0)*ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A ))),0),ROW()+COUNTA($A:$A)) Copy that formula down until you receive an error value. Using the above data, B1 returns 53 Is that something you can work with? Post back with more questions. ---------------------- Regards, Ron Microsoft MVP (Excel) "larry brunster" <larry wrote in message ... I'm looking for a way to automaticaly find missing values in a column of numbers. Here's an example (all values are in the same column): 50 51 52 54 55 Value 53 is missing from the column (which should be increasing by 1 every row). Is there a way to automatically detect that there is a gap between 2 values? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the first and last column with values | Excel Discussion (Misc queries) | |||
Finding test strings in a row which are missing from a column | Excel Worksheet Functions | |||
Finding (Multiple) Highest Values in Column | Excel Worksheet Functions | |||
Finding Unique Values in Column | Excel Discussion (Misc queries) | |||
Finding the max of a column where values less than a specified number | Excel Worksheet Functions |