Finding missing numbers in a list

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lynne - 08 Nov 2006 21:50 GMT

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

Reply to this Message

Dave Peterson - 08 Nov 2006 22:04 GMT

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.

> I have a series of client account numbers that range from 001-999. I
> first imported the client information from Timeslips which where we
[quoted text clipped - 6 lines]
>
> Lynne

Signature

Dave Peterson

Reply to this Message

Lynne - 08 Nov 2006 22:43 GMT

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

> If you're just looking for a report...
>
[quoted text clipped - 21 lines]
> >
> > Lynne

Reply to this Message

Dave Peterson - 08 Nov 2006 23:11 GMT

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.

> Dave,
>
[quoted text clipped - 36 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Max - 09 Nov 2006 02:16 GMT

> .. 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.
> > .. I have a series of client account numbers that range from 001-999.

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.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Max - 09 Nov 2006 02:37 GMT

> All the unused / missing client numbers will be extracted at the top in
> col F.

The unused / missing numbers will appear neatly in ascending order

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Max - 09 Nov 2006 06:57 GMT

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)

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Max - 09 Nov 2006 07:09 GMT

Further oops ..

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

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Lynne - 09 Nov 2006 15:42 GMT

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.

Lynne

> > .. My first client number starts with
> > 001,002,004.........456,457,459,461.....
[quoted text clipped - 50 lines]
> xdemechanik
> ---

Reply to this Message

Max - 09 Nov 2006 15:59 GMT

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.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hi Max,
>
[quoted text clipped - 6 lines]
>
> Lynne

Reply to this Message

Max - 09 Nov 2006 22:49 GMT

Received response from OP ..
--- Lynne <lynne@adlerpias.com> wrote:

> Max,
>
> Thank you so much for you, that was the formula's
> that I was looking for.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Biff - 09 Nov 2006 06:48 GMT

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

>I have a series of client account numbers that range from 001-999. I
> first imported the client information from Timeslips which where we
[quoted text clipped - 6 lines]
>
> Lynne

Reply to this Message

Lynne - 09 Nov 2006 15:35 GMT

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.

Lynne

> Similar to Max's suggestion but only uses one formula:
>
[quoted text clipped - 23 lines]
> >
> > Lynne

Reply to this Message

Biff - 09 Nov 2006 19:03 GMT

>If the missing values have been returned where
>does excel put them?

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

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

> Hi Biff,
>
[quoted text clipped - 34 lines]
>> >
>> > Lynne

Reply to this Message