Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
filter for missing values in a range
I have a list in Col A ( about 2000) one to four digit numbers. I would like
to ( for example) , in the 700's be able to filter and find out which of the numbers from 700 to 799 is NOT in the list. I know how to copy and paste to another col, etc. Thanks in advance for the wonderful help all of you give. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
filter for missing values in a range
One play ..
Assume your source data runs in A2 down In B2: =IF(ROWS($1:1)+700-1799,"",IF(ISNUMBER(MATCH(ROWS($1:1)+700-1,A:A,0)),"",ROWS($1:1)+700-1)) Copy B2 down by 100 rows to cover the full spread of numbers to be checked, ie # of nums from start num to end num (700 to 799). Note that the extent of source data in col A does not matter in the copy -down. Then place in C2: =IF(ROWS($1:1)COUNT(B:B),"",SMALL(B:B,ROWS($1:1)) ) Copy C2 down just enough ie until blanks are returned. All the missing nums in between the start to end nums will appear neatly packed at the top. Wave your success, hit YES below .. -- Max Singapore --- "billnock" wrote: I have a list in Col A ( about 2000) one to four digit numbers. I would like to ( for example) , in the 700's be able to filter and find out which of the numbers from 700 to 799 is NOT in the list. I know how to copy and paste to another col, etc. Thanks in advance for the wonderful help all of you give. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
missing data w/ advanced filter | Excel Worksheet Functions | |||
Pivot Table Missing Filter Tab | Excel Discussion (Misc queries) | |||
missing or ill egal extract range advanced filter | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
Excel Compare values in columns & display missing values in a new | Excel Discussion (Misc queries) |