Displaying Numbers Missing In A Sequence

I'm importing into Excel a column of data that represents
4-digit phone extensions. The range is from 1111-9999,
sorted from top to bottom. Not every number is used
though.
What I'm trying to do is count the column automatically
and have it tell me in a separate cell at the bottom which
numbers in the sequence are missing (representing
available extensions).
Does anyone know a way to do this? We'd like to be able
to automate this process for our department, rather than
have to rummage through the column of data manually.
Any help would be appreciated.
-Rick

One method to do this (though NOT automated) is to
configure a column with an IF statement such as:
IF(cell_1cell_2-1, cell_1+1,0)
This will print missing extensions in the column (with
zero values not displayed). It will not address multi-
digit spans. However, you could write the IF
statemnet/function such that it processes for a predefined
number of values. Configure a print macro all non-zero
values in the column and... voila!

Variations of this theme have worked for me. Your mileage
may vary.





-----Original Message-----
I'm importing into Excel a column of data that represents
4-digit phone extensions. The range is from 1111-9999,
sorted from top to bottom. Not every number is used
though.
What I'm trying to do is count the column automatically
and have it tell me in a separate cell at the bottom
which
numbers in the sequence are missing (representing
available extensions).
Does anyone know a way to do this? We'd like to be able
to automate this process for our department, rather than
have to rummage through the column of data manually.
Any help would be appreciated.
-Rick

.

"Rick D" wrote...
I'm importing into Excel a column of data that represents
4-digit phone extensions. The range is from 1111-9999,
sorted from top to bottom. Not every number is used
though.
What I'm trying to do is count the column automatically
and have it tell me in a separate cell at the bottom which
numbers in the sequence are missing (representing
available extensions).

So you don't have extentions with leading zeros or zeros after leading ones? For
example, neither 0100 nor 1101 would be valid extentions? Would zeros be valid
in any extensions? For the sake of argument, I'll assume 1111 is the lowest
numeric extention, but higher numeric extentions could have zeros.

One cell for *all* missing numbers or one cell for *each* missing number? I'll
assume the latter. If the list of extentions you have were in a range named List
and the topmost (lowest missing numeric value) were generated in cell C2, you
could find the first missing extention using the array formula

C2:
=MATCH(0,COUNTIF(List,ROW(INDIRECT("1111:9999"))), 0)+1110

and the second missing extention in the cell below it using the array formula

C3:
=MATCH(0,COUNTIF(C$2:C2,ROW(INDIRECT("1111:9999")) )
+COUNTIF(List,ROW(INDIRECT("11:99"))),0)+1110

Drag C3 down until it generates #N/A.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

In article ,
says...

One cell for *all* missing numbers or one cell for *each* missing number? I'll
assume the latter. If the list of extentions you have were in a range named List
and the topmost (lowest missing numeric value) were generated in cell C2, you
could find the first missing extention using the array formula

C2:
=MATCH(0,COUNTIF(List,ROW(INDIRECT("1111:9999"))), 0)+1110

and the second missing extention in the cell below it using the array formula

C3:
=MATCH(0,COUNTIF(C$2:C2,ROW(INDIRECT("1111:9999")) )
+COUNTIF(List,ROW(INDIRECT("11:99"))),0)+1110


I don't know how you figure out these formulas. My head hurts just
trying to understand them. In the event that I did understand the
formula, you need 1111:9999 rather than 11:99. Correct?

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
"Rick D" wrote...
I'm importing into Excel a column of data that represents
4-digit phone extensions. The range is from 1111-9999,
sorted from top to bottom. Not every number is used
though.
What I'm trying to do is count the column automatically
and have it tell me in a separate cell at the bottom which
numbers in the sequence are missing (representing
available extensions).

So you don't have extentions with leading zeros or zeros after leading ones? For
example, neither 0100 nor 1101 would be valid extentions? Would zeros be valid
in any extensions? For the sake of argument, I'll assume 1111 is the lowest
numeric extention, but higher numeric extentions could have zeros.

One cell for *all* missing numbers or one cell for *each* missing number? I'll
assume the latter. If the list of extentions you have were in a range named List
and the topmost (lowest missing numeric value) were generated in cell C2, you
could find the first missing extention using the array formula

C2:
=MATCH(0,COUNTIF(List,ROW(INDIRECT("1111:9999"))), 0)+1110

and the second missing extention in the cell below it using the array formula

C3:
=MATCH(0,COUNTIF(C$2:C2,ROW(INDIRECT("1111:9999")) )
+COUNTIF(List,ROW(INDIRECT("11:99"))),0)+1110

Drag C3 down until it generates #N/A.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

"Tushar Mehta" wrote...
...
C3:
=MATCH(0,COUNTIF(C$2:C2,ROW(INDIRECT("1111:9999" )))
+COUNTIF(List,ROW(INDIRECT("11:99"))),0)+1110

I don't know how you figure out these formulas. My head hurts just
trying to understand them. In the event that I did understand the
formula, you need 1111:9999 rather than 11:99. Correct?

Correct. C3 formula should be

=MATCH(0,COUNTIF(C$2:C2,ROW(INDIRECT("1111:9999")) )
+COUNTIF(List,ROW(INDIRECT("1111:9999"))),0)+1110

So much for testing on a subrange, copying and pasting into the ng response and
editing the copy. Anyway, this one's easy. For Y a range or array, COUNTIF(X,Y)
returns a range of the same shape and size as Y containing counts of each entry
in Y found in X.

You know, after re-reading my original message, I realized
that the range should've been 1000-9999. So 1101 would be
a valid extension. The list of missing numbers doesn't
have to be reported all in one cell - it can populate
another column, or fall under the original data list.
I'll try your formula to see if I can get it to work -
thanks.


-----Original Message-----
"Rick D" wrote...
I'm importing into Excel a column of data that
represents
4-digit phone extensions. The range is from 1111-9999,
sorted from top to bottom. Not every number is used
though.
What I'm trying to do is count the column automatically
and have it tell me in a separate cell at the bottom
which
numbers in the sequence are missing (representing
available extensions).

So you don't have extentions with leading zeros or zeros
after leading ones? For
example, neither 0100 nor 1101 would be valid extentions?
Would zeros be valid
in any extensions? For the sake of argument, I'll assume
1111 is the lowest
numeric extention, but higher numeric extentions could
have zeros.

One cell for *all* missing numbers or one cell for *each*
missing number? I'll
assume the latter. If the list of extentions you have
were in a range named List
and the topmost (lowest missing numeric value) were
generated in cell C2, you
could find the first missing extention using the array
formula

C2:
=MATCH(0,COUNTIF(List,ROW(INDIRECT("1111:9999"))) ,0)+1110

and the second missing extention in the cell below it
using the array formula

C3:
=MATCH(0,COUNTIF(C$2:C2,ROW(INDIRECT("1111:9999") ))
+COUNTIF(List,ROW(INDIRECT("11:99"))),0)+1110

Drag C3 down until it generates #N/A.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google
newsgroup archives.
.

"Rick D" wrote...
You know, after re-reading my original message, I realized
that the range should've been 1000-9999. So 1101 would be
a valid extension. The list of missing numbers doesn't
have to be reported all in one cell - it can populate
another column, or fall under the original data list.
...

If you need to do this often, you'd be much better off buying at least one copy
of Office Professional to get Access. Pulling entries in one list that don't
appear in another is quick and simple in Access. Given the entire list of
extentions in a table named Entire and the current partial working list of
extentions in a table named Partial, the SQL to find all extentions not in
Partial is just

SELECT Entire.Ext
FROM Entire LEFT JOIN Partial ON Entire.Ext = Partial.Ext
WHERE Partial.Ext Is Null;

Access (or any other mostly relational database) is a MUCH BETTER tool for this
task than Excel.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

Well, as much fun as this exercise was, I'm not sure why Rick gets just
a list of 'in use' phone numbers. I'm sure that somewhere, in some
database, is a list of phone numbers with their current status. All
one needs to do is pull the list of numbers with a status="Vacant" (or
"Available" or whatever). [The database probably doesn't have two
tables with phone numbers; just the one.]

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
"Rick D" wrote...
You know, after re-reading my original message, I realized
that the range should've been 1000-9999. So 1101 would be
a valid extension. The list of missing numbers doesn't
have to be reported all in one cell - it can populate
another column, or fall under the original data list.
..

If you need to do this often, you'd be much better off buying at least one copy
of Office Professional to get Access. Pulling entries in one list that don't
appear in another is quick and simple in Access. Given the entire list of
extentions in a table named Entire and the current partial working list of
extentions in a table named Partial, the SQL to find all extentions not in
Partial is just

SELECT Entire.Ext
FROM Entire LEFT JOIN Partial ON Entire.Ext = Partial.Ext
WHERE Partial.Ext Is Null;

Access (or any other mostly relational database) is a MUCH BETTER tool for this
task than Excel.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.