filter for missing values in a range

  • Thread starter Thread starter billnock
  • Start date Start date
B

billnock

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.
 
One play ..
Assume your source data runs in A2 down
In B2
=IF(ROWS($1:1)+700-1>799,"",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 ..
 
Back
Top