Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Finding numbers missing from a sequence
I've just been asked if there's a way Excel can easily find if there are
missing numbers from a sequence of numbers in a column. For example, the column contains numbers between 1000 and 2000, but the user isn't sure if every number between 1000 and 2000 is present in the column, to possibly make it more interesting, each number may appear more than once in the column. Any ideas? Cheers, Andy |
#2
|
|||
|
|||
use a helper worksheet
starting in cell A1, number from 1000 through 2000. starting in b1, use a formula =COUNTIF('good data'!$A$1:$A$2000,'helper sheet'!A1) copy formula from B1:B1000 now do an autofilter on column B. Filter for value of 0. "andy" wrote: I've just been asked if there's a way Excel can easily find if there are missing numbers from a sequence of numbers in a column. For example, the column contains numbers between 1000 and 2000, but the user isn't sure if every number between 1000 and 2000 is present in the column, to possibly make it more interesting, each number may appear more than once in the column. Any ideas? Cheers, Andy |
#3
|
|||
|
|||
This is how I'd do it that is pretty easy:
Say you have the numbers 2, 4 and 6 in the range A1:A3. You want to know which numbers from 1 to 10 are missing. -Enter the numbers 1 to 10 (use the Fill handle) in C1:C10. -Enter the formula: =IF(ISERROR(MATCH(C1,$A$1:$A$3,FALSE)),"x","") in D1 and copy it down to D10. Now every "x" represents a missing number. -- Jim "andy" wrote in message ... | I've just been asked if there's a way Excel can easily find if there are | missing numbers from a sequence of numbers in a column. | | For example, the column contains numbers between 1000 and 2000, but the user | isn't sure if every number between 1000 and 2000 is present in the column, | to possibly make it more interesting, each number may appear more than once | in the column. | | Any ideas? | | Cheers, | | Andy | | |
#4
|
|||
|
|||
Sorry, only just checked back for replies.
Thanks to both of you for your help. Andy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repeating a sequence of numbers down a column | Excel Discussion (Misc queries) | |||
finding numbers | New Users to Excel | |||
finding data between two numbers (1000-1999)and totaling correspo. | Excel Discussion (Misc queries) | |||
Finding Numbers with Cells that also contain letters | Excel Discussion (Misc queries) | |||
finding common numbers in large lists | Excel Worksheet Functions |