Displaying Numbers Missing In A Sequence

  • Thread starter Thread starter Rick D
  • Start date Start date
R

Rick D

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_1<>cell_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.

(e-mail address removed)
 
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).
Click to expand...

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.
 
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
Click to expand...

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
 
...
...
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?
Click to expand...

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.
 
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.
Click to expand...
...

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.
 
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
 
Back
Top