I have items that have been assigned an ID number. How can I extract a list
of unassigned numbers? I.E. I have number range starting with
7200000-7232000. How can I extract a list of unassigned numbers from this
list of numbers? I know some of the Excel functions but I could not figure
this out.
--
nfw

Hi,
you have your number in column A, in column B enter the complete list of
numbers then in column C enter

=IF(COUNTIF(A$1:A$5,B$1:B$100)=1,"",B1)

copy down

change range to fit your needs

"Nancy" wrote:

> I have items that have been assigned an ID number. How can I extract a list
> of unassigned numbers? I.E. I have number range starting with
> 7200000-7232000. How can I extract a list of unassigned numbers from this
> list of numbers? I know some of the Excel functions but I could not figure
> this out.
> --
> nfw

Assume your source data runs in A2 down
In B2
=IF(ROWS($1:1)+7200000-1>7232000,"",IF(ISNUMBER(MATCH(ROWS($1:1)+7200000-1,A:A,0)),"",ROWS($1:1)+7200000-1))
Copy B2 down to cover the full spread of numbers (# of nums from start num
to end num), ie by 32k rows

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.
Success? wave it, hit the YES below.
--
Max
Singapore
---
"Nancy" wrote:
> I have items that have been assigned an ID number. How can I extract a list
> of unassigned numbers? I.E. I have number range starting with
> 7200000-7232000. How can I extract a list of unassigned numbers from this
> list of numbers? I know some of the Excel functions but I could not figure
> this out