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

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.



Lynne wrote:
>
> 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

--

Dave Peterson

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
Dave Peterson wrote:
> 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.
>
>
>
> Lynne wrote:
> >
> > 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
>
> --
>
> 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.

Lynne wrote:
>
> 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
> Dave Peterson wrote:
> > 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.
> >
> >
> >
> > Lynne wrote:
> > >
> > > 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
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

"Lynne" <(E-Mail Removed)> wrote
> .. 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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

"Lynne" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

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())))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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()))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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
Biff wrote:
> 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
>
> "Lynne" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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
> >