Finding missing numbers in a list

L

Lynne

I have a series of client account numbers that range from 001-999. I
first imported the client information from Timeslips which where we
keep client information into an Excel spreadsheet. What I need to now
do is create a macro or a query that will tell me what numbers are not
being utilized from the spreadsheet that I created but I don't know how
to do this. I have only created one macro before and that was with
help from an instructor so I don't know where to begin can someone
please help me with this.

Lynne
 
D

Dave Peterson

If you're just looking for a report...

Create a new worksheet
Put nice headers in A1 and B1
In A2, put
=row()-1
and drag down to A101

Then in B2, put:
=isnumber(match(a2,sheet1!a:a,0))

You'll see True if it appears in your list. False if it's missing.

You could apply data|filter|autofilter to column B to see the missing/used.
 
L

Lynne

Dave,

Thanks for your reply but I don't quite understand what you are talking
about. If I use the formulas you posted it does not help me find the
missing client numbers in my current spreadsheet. Maybe I need to
clarify what I am trying to do. My first client number starts with
001,002,004.........456,457,459,461..........I am missing numbers in
between so I need to know what formula to use or how to run a query to
find the unused numbers to asign them to new clients.

Lynne
 
D

Dave Peterson

If you use those formulas in that other sheet, you'll end up with a bunch of
True/falses in column B of that new sheet.

You can use data|Filter|autofilter on column B to show just the falses. These
are the numbers that are missing.
 
M

Max

Lynne said:
.. My first client number starts with
001,002,004.........456,457,459,461.....
.....I am missing numbers in between so I need to know
what formula to use or how to run a query to
find the unused numbers to asign them to new clients.
Click to expand...

Think your client numbers are probably text numbers

Assume the text client numbers are listed in A1 down, eg:

002
003
007
015
016
019

etc

(the client numbers listed in col A can be unsorted)

Assume the max client number issuable for the numbers listed in col A is
say: 1000 (this number must be known)

Using 2 empty cols to the right, eg cols E and F

Put in E1, and array-enter** the formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=IF(ISNUMBER(MATCH(ROW(),$A$1:$A$1000+0,0)),"",ROW())

The range A1:A1000 corresponds to the full assumed range size of 1000. Adapt
the range to suit the max client number issuable. The "+0" in the part:
$A$1:$A$1000+0 is to coerce the text numbers in col A to real numbers for
the purpose.

**In the formula bar, look for the curly braces: { } around the formula
which Excel will insert upon correct array-entering. If you don't see the
curly braces, then it hasn't been correctly array entered. If so, click
inside the formula bar, and try it again (press CTRL+SHIFT+ENTER).

Then place in F1, press ENTER will do:
=IF(ROW(A1)>COUNT(E:E),"",INDEX(E:E,SMALL(E:E,ROW(A1)))-1)
Format F1 as Custom, type: 000

Select E1:F1, copy down to F1000.
All the unused / missing client numbers will be extracted at the top in col
F.
 
B

Biff

Similar to Max's suggestion but only uses one formula:

Sequence range is 001:999. Also assuming that the numbers are really TEXT
due to the leading 0's.

Numbers in the range A1:Ax

Array entered:

=SMALL(IF(ISNA(MATCH(ROW($1:$999),A$1:A$x+0,0)),ROW($1:$999)),ROW(A1))

Copy down until you get #NUM! errors meaning all the missing values have
been returned. You can custom format the cells as: 000 to keep the leading
0's.

Biff
 
M

Max

Errata for F1's formula, my apologies ..
Then place in F1, press ENTER will do:
=IF(ROW(A1)>COUNT(E:E),"",INDEX(E:E,SMALL(E:E,ROW(A1)))-1)
Click to expand...

In F1 should be:
=IF(ROW()>COUNT(E:E),"",INDEX(E:E,SMALL(E:E,ROW())))
 
M

Max

Further oops ..
In F1 should be:
=IF(ROW()>COUNT(E:E),"",INDEX(E:E,SMALL(E:E,ROW())))
Click to expand...

Suffices to have it in F1 as:
=IF(ROW()>COUNT(E:E),"",SMALL(E:E,ROW()))
 
L

Lynne

Hi Biff,

Thanks for your help and I placed the formula you gave me in my
spreadsheet but I must have something wrong because I got #NUM error in
the second cell when I copied the formula down. If the missing values
have been returned where does excel put them? I am sorry for all of
the questions but I am not very experienced with these types of
formula's. I really appreciate everyone's suggestions.
 
L

Lynne

Hi Max,

I tried your formula and the first one worked but when I placed the
second formula in F1, I received an error message,
=IF(ROW(A1>COUNT(E:E),"",INDEX(E:E,SMALL(F:F,ROW(A1)))-1), where the
two quotation marks are in the formula. Do you have any suggestions of
what I might have done wrong? I really do appreciate all the help I am
getting regarding this issue.
 
M

Max

Lynne,
As explained in my follow ups, there was an error earlier for the formula in
F1.
Place instead in F1, press ENTER will do:
=IF(ROW()>COUNT(E:E),"",SMALL(E:E,ROW()))
It should work fine. Try it. Let me know.
 
B

Biff

If the missing values have been returned where
does excel put them?
Click to expand...

The formula returns the missing values so Excel puts them wherever you put
the formula.
I got #NUM error in the second cell when I
copied the formula down
Click to expand...

Did you enter the formula s an array? Enter it with the key combination of
CTRL,SHIFT,ENTER (not just ENTER).

Would you like me to post a sample file?

Biff
 
Top