If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can I extract a list of numbers missing in a list?
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 |
#2
|
|||
|
|||
How can I extract a list of numbers missing in a list?
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 |
#3
|
|||
|
|||
How can I extract a list of numbers missing in a list?
Assume your source data runs in A2 down
In B2: =IF(ROWS($1:1)+7200000-17232000,"",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 |
Thread Tools | |
Display Modes | |
|
|