I am making a schedule in Excel and have work areas numbered 1 through 17.

My rows list each employee and the columns list the day of the week, I want
to check myself that I have all areas covered. Is there a simple function
that would tell me if I was missing a number in a column (day of the month)
and which was missing? Here is an example of what I have set-up:
June
1 2 3 4
Name
Jack Handy 1 2 3 3
Hem Roids 3 4 2 1
Buck Shot 5 6 1 3

Work areas: 1=bench a 4=bench d
2=bench b 5=bench e
3=bench c

Thanks

Let's see if we understand this.

You should have the numbers 1 through 17 listed in a column. You want to
check and make sure every one of those numbers is present. List any that are
missing.

Is that what you want?

Assume the range for the numbers is B2:B18

Try this array** formula:

=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$17,0)),ROW( $1:$17)),ROWS($1:1))

Copy down until you get #NUM! errors meaning all missing numbers have been
returned.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Lost in Microbiology" wrote
in message ...
I am making a schedule in Excel and have work areas numbered 1 through 17.

My rows list each employee and the columns list the day of the week, I
want
to check myself that I have all areas covered. Is there a simple function
that would tell me if I was missing a number in a column (day of the
month)
and which was missing? Here is an example of what I have set-up:
June
1 2 3 4
Name
Jack Handy 1 2 3 3
Hem Roids 3 4 2 1
Buck Shot 5 6 1 3

Work areas: 1=bench a 4=bench d
2=bench b 5=bench e
3=bench c

Thanks

Ooops!

Typo:

Assume the range for the numbers is B2:B18
=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$17,0)),ROW ($1:$17)),ROWS($1:1))

Should be:

=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$18,0)),ROW( $1:$17)),ROWS($1:1))

Biff

"T. Valko" wrote in message
...
Let's see if we understand this.

You should have the numbers 1 through 17 listed in a column. You want to
check and make sure every one of those numbers is present. List any that
are missing.

Is that what you want?

Assume the range for the numbers is B2:B18

Try this array** formula:

=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$17,0)),ROW( $1:$17)),ROWS($1:1))

Copy down until you get #NUM! errors meaning all missing numbers have been
returned.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Lost in Microbiology"
wrote in message
...
I am making a schedule in Excel and have work areas numbered 1 through 17.

My rows list each employee and the columns list the day of the week, I
want
to check myself that I have all areas covered. Is there a simple function
that would tell me if I was missing a number in a column (day of the
month)
and which was missing? Here is an example of what I have set-up:
June
1 2 3 4
Name
Jack Handy 1 2 3 3
Hem Roids 3 4 2 1
Buck Shot 5 6 1 3

Work areas: 1=bench a 4=bench d
2=bench b 5=bench e
3=bench c

Thanks

Thanks, that will work, but it is a lot of labor to check 30 columns(a
month's schedule) in that fashion. The other wrinkle I have found, are people
who work multiple locations, i.e. a cell can have a 1 and a 15. Is this just
not possible?

I tried doing a column with my required numbers and placing a
=countif(c2:c12, 1)0 which returns TRUE/FALSE, and I can just copy and
paste across to get my answer. But when multiple numbers are present it
throws the function off and it only returns a FALSE.


"T. Valko" wrote:

Ooops!

Typo:

Assume the range for the numbers is B2:B18
=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$17,0)),ROW ($1:$17)),ROWS($1:1))

Should be:

=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$18,0)),ROW( $1:$17)),ROWS($1:1))

Biff

"T. Valko" wrote in message
...
Let's see if we understand this.

You should have the numbers 1 through 17 listed in a column. You want to
check and make sure every one of those numbers is present. List any that
are missing.

Is that what you want?

Assume the range for the numbers is B2:B18

Try this array** formula:

=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$17,0)),ROW( $1:$17)),ROWS($1:1))

Copy down until you get #NUM! errors meaning all missing numbers have been
returned.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Lost in Microbiology"
wrote in message
...
I am making a schedule in Excel and have work areas numbered 1 through 17.

My rows list each employee and the columns list the day of the week, I
want
to check myself that I have all areas covered. Is there a simple function
that would tell me if I was missing a number in a column (day of the
month)
and which was missing? Here is an example of what I have set-up:
June
1 2 3 4
Name
Jack Handy 1 2 3 3
Hem Roids 3 4 2 1
Buck Shot 5 6 1 3

Work areas: 1=bench a 4=bench d
2=bench b 5=bench e
3=bench c

Thanks

a cell can have a 1 and a 15.

Eh, that's a buzz kill!

How would that 1 and 15 in the same cell appear? Is there a space between
them? A comma? A comma and a space?

1 15
1,15
1, 15

Biff

"Lost in Microbiology" wrote
in message ...
Thanks, that will work, but it is a lot of labor to check 30 columns(a
month's schedule) in that fashion. The other wrinkle I have found, are
people
who work multiple locations, i.e. a cell can have a 1 and a 15. Is this
just
not possible?

I tried doing a column with my required numbers and placing a
=countif(c2:c12, 1)0 which returns TRUE/FALSE, and I can just copy and
paste across to get my answer. But when multiple numbers are present it
throws the function off and it only returns a FALSE.


"T. Valko" wrote:

Ooops!

Typo:

Assume the range for the numbers is B2:B18
=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$17,0)),ROW ($1:$17)),ROWS($1:1))

Should be:

=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$18,0)),ROW( $1:$17)),ROWS($1:1))

Biff

"T. Valko" wrote in message
...
Let's see if we understand this.

You should have the numbers 1 through 17 listed in a column. You want
to
check and make sure every one of those numbers is present. List any
that
are missing.

Is that what you want?

Assume the range for the numbers is B2:B18

Try this array** formula:

=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$17,0)),ROW( $1:$17)),ROWS($1:1))

Copy down until you get #NUM! errors meaning all missing numbers have
been
returned.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Lost in Microbiology"
wrote in message
...
I am making a schedule in Excel and have work areas numbered 1 through
17.

My rows list each employee and the columns list the day of the week, I
want
to check myself that I have all areas covered. Is there a simple
function
that would tell me if I was missing a number in a column (day of the
month)
and which was missing? Here is an example of what I have set-up:
June
1 2 3 4
Name
Jack Handy 1 2 3 3
Hem Roids 3 4 2 1
Buck Shot 5 6 1 3

Work areas: 1=bench a 4=bench d
2=bench b 5=bench e
3=bench c

Thanks

Sorry for the buzz kill, I am working with someone else on this, and we might
change that. For now, the numbers are comma separated.



"T. Valko" wrote:

a cell can have a 1 and a 15.

Eh, that's a buzz kill!

How would that 1 and 15 in the same cell appear? Is there a space between
them? A comma? A comma and a space?

1 15
1,15
1, 15

Biff

"Lost in Microbiology" wrote
in message ...
Thanks, that will work, but it is a lot of labor to check 30 columns(a
month's schedule) in that fashion. The other wrinkle I have found, are
people
who work multiple locations, i.e. a cell can have a 1 and a 15. Is this
just
not possible?

I tried doing a column with my required numbers and placing a
=countif(c2:c12, 1)0 which returns TRUE/FALSE, and I can just copy and
paste across to get my answer. But when multiple numbers are present it
throws the function off and it only returns a FALSE.


"T. Valko" wrote:

Ooops!

Typo:

Assume the range for the numbers is B2:B18
=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$17,0)),ROW ($1:$17)),ROWS($1:1))

Should be:

=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$18,0)),ROW( $1:$17)),ROWS($1:1))

Biff

"T. Valko" wrote in message
...
Let's see if we understand this.

You should have the numbers 1 through 17 listed in a column. You want
to
check and make sure every one of those numbers is present. List any
that
are missing.

Is that what you want?

Assume the range for the numbers is B2:B18

Try this array** formula:

=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$17,0)),ROW( $1:$17)),ROWS($1:1))

Copy down until you get #NUM! errors meaning all missing numbers have
been
returned.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Lost in Microbiology"
wrote in message
...
I am making a schedule in Excel and have work areas numbered 1 through
17.

My rows list each employee and the columns list the day of the week, I
want
to check myself that I have all areas covered. Is there a simple
function
that would tell me if I was missing a number in a column (day of the
month)
and which was missing? Here is an example of what I have set-up:
June
1 2 3 4
Name
Jack Handy 1 2 3 3
Hem Roids 3 4 2 1
Buck Shot 5 6 1 3

Work areas: 1=bench a 4=bench d
2=bench b 5=bench e
3=bench c

Thanks

Well, that makes it more complicated!

This will work but it's not real elegant. I'm also assuming that no cell
will contain more than 2 numbers.

See this screencap:

http://img137.imageshack.us/img137/5363/missinggp3.jpg

Here's the formula that's been entered in cell G2 and copied down to G18:

=IF(SUMPRODUCT(--(MID(C$2:C$12,FIND(",",C$2:C$12&","),10)=","&ROWS( $1:1)))+SUMPRODUCT(--(LEFT(C$2:C$12&",",FIND(",",C$2:C$12&","))=ROWS($1 :1)&",")),"ok","missing")

Biff

"Lost in Microbiology" wrote
in message ...
Sorry for the buzz kill, I am working with someone else on this, and we
might
change that. For now, the numbers are comma separated.



"T. Valko" wrote:

a cell can have a 1 and a 15.

Eh, that's a buzz kill!

How would that 1 and 15 in the same cell appear? Is there a space between
them? A comma? A comma and a space?

1 15
1,15
1, 15

Biff

"Lost in Microbiology"
wrote
in message ...
Thanks, that will work, but it is a lot of labor to check 30 columns(a
month's schedule) in that fashion. The other wrinkle I have found, are
people
who work multiple locations, i.e. a cell can have a 1 and a 15. Is this
just
not possible?

I tried doing a column with my required numbers and placing a
=countif(c2:c12, 1)0 which returns TRUE/FALSE, and I can just copy and
paste across to get my answer. But when multiple numbers are present it
throws the function off and it only returns a FALSE.


"T. Valko" wrote:

Ooops!

Typo:

Assume the range for the numbers is B2:B18
=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$17,0)),ROW ($1:$17)),ROWS($1:1))

Should be:

=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$18,0)),ROW( $1:$17)),ROWS($1:1))

Biff

"T. Valko" wrote in message
...
Let's see if we understand this.

You should have the numbers 1 through 17 listed in a column. You
want
to
check and make sure every one of those numbers is present. List any
that
are missing.

Is that what you want?

Assume the range for the numbers is B2:B18

Try this array** formula:

=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$17,0)),ROW( $1:$17)),ROWS($1:1))

Copy down until you get #NUM! errors meaning all missing numbers
have
been
returned.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Lost in Microbiology"

wrote in message
...
I am making a schedule in Excel and have work areas numbered 1
through
17.

My rows list each employee and the columns list the day of the
week, I
want
to check myself that I have all areas covered. Is there a simple
function
that would tell me if I was missing a number in a column (day of
the
month)
and which was missing? Here is an example of what I have set-up:
June
1 2 3 4
Name
Jack Handy 1 2 3 3
Hem Roids 3 4 2 1
Buck Shot 5 6 1 3

Work areas: 1=bench a 4=bench d
2=bench b 5=bench e
3=bench c

Thanks

Bloody brilliant, thanks so much!

"T. Valko" wrote:

Well, that makes it more complicated!

This will work but it's not real elegant. I'm also assuming that no cell
will contain more than 2 numbers.

See this screencap:

http://img137.imageshack.us/img137/5363/missinggp3.jpg

Here's the formula that's been entered in cell G2 and copied down to G18:

=IF(SUMPRODUCT(--(MID(C$2:C$12,FIND(",",C$2:C$12&","),10)=","&ROWS( $1:1)))+SUMPRODUCT(--(LEFT(C$2:C$12&",",FIND(",",C$2:C$12&","))=ROWS($1 :1)&",")),"ok","missing")

Biff

"Lost in Microbiology" wrote
in message ...
Sorry for the buzz kill, I am working with someone else on this, and we
might
change that. For now, the numbers are comma separated.



"T. Valko" wrote:

a cell can have a 1 and a 15.

Eh, that's a buzz kill!

How would that 1 and 15 in the same cell appear? Is there a space between
them? A comma? A comma and a space?

1 15
1,15
1, 15

Biff

"Lost in Microbiology"
wrote
in message ...
Thanks, that will work, but it is a lot of labor to check 30 columns(a
month's schedule) in that fashion. The other wrinkle I have found, are
people
who work multiple locations, i.e. a cell can have a 1 and a 15. Is this
just
not possible?

I tried doing a column with my required numbers and placing a
=countif(c2:c12, 1)0 which returns TRUE/FALSE, and I can just copy and
paste across to get my answer. But when multiple numbers are present it
throws the function off and it only returns a FALSE.


"T. Valko" wrote:

Ooops!

Typo:

Assume the range for the numbers is B2:B18
=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$17,0)),ROW ($1:$17)),ROWS($1:1))

Should be:

=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$18,0)),ROW( $1:$17)),ROWS($1:1))

Biff

"T. Valko" wrote in message
...
Let's see if we understand this.

You should have the numbers 1 through 17 listed in a column. You
want
to
check and make sure every one of those numbers is present. List any
that
are missing.

Is that what you want?

Assume the range for the numbers is B2:B18

Try this array** formula:

=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$17,0)),ROW( $1:$17)),ROWS($1:1))

Copy down until you get #NUM! errors meaning all missing numbers
have
been
returned.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Lost in Microbiology"

wrote in message
...
I am making a schedule in Excel and have work areas numbered 1
through
17.

My rows list each employee and the columns list the day of the
week, I
want
to check myself that I have all areas covered. Is there a simple
function
that would tell me if I was missing a number in a column (day of
the
month)
and which was missing? Here is an example of what I have set-up:
June
1 2 3 4
Name
Jack Handy 1 2 3 3
Hem Roids 3 4 2 1
Buck Shot 5 6 1 3

Work areas: 1=bench a 4=bench d
2=bench b 5=bench e
3=bench c

Thanks

You're welcome. Thanks for the feedback!

Biff

"Lost in Microbiology" wrote
in message ...
Bloody brilliant, thanks so much!

"T. Valko" wrote:

Well, that makes it more complicated!

This will work but it's not real elegant. I'm also assuming that no cell
will contain more than 2 numbers.

See this screencap:

http://img137.imageshack.us/img137/5363/missinggp3.jpg

Here's the formula that's been entered in cell G2 and copied down to G18:

=IF(SUMPRODUCT(--(MID(C$2:C$12,FIND(",",C$2:C$12&","),10)=","&ROWS( $1:1)))+SUMPRODUCT(--(LEFT(C$2:C$12&",",FIND(",",C$2:C$12&","))=ROWS($1 :1)&",")),"ok","missing")

Biff

"Lost in Microbiology"
wrote
in message ...
Sorry for the buzz kill, I am working with someone else on this, and we
might
change that. For now, the numbers are comma separated.



"T. Valko" wrote:

a cell can have a 1 and a 15.

Eh, that's a buzz kill!

How would that 1 and 15 in the same cell appear? Is there a space
between
them? A comma? A comma and a space?

1 15
1,15
1, 15

Biff

"Lost in Microbiology"
wrote
in message ...
Thanks, that will work, but it is a lot of labor to check 30
columns(a
month's schedule) in that fashion. The other wrinkle I have found,
are
people
who work multiple locations, i.e. a cell can have a 1 and a 15. Is
this
just
not possible?

I tried doing a column with my required numbers and placing a
=countif(c2:c12, 1)0 which returns TRUE/FALSE, and I can just copy
and
paste across to get my answer. But when multiple numbers are present
it
throws the function off and it only returns a FALSE.


"T. Valko" wrote:

Ooops!

Typo:

Assume the range for the numbers is B2:B18
=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$17,0)),ROW ($1:$17)),ROWS($1:1))

Should be:

=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$18,0)),ROW( $1:$17)),ROWS($1:1))

Biff

"T. Valko" wrote in message
...
Let's see if we understand this.

You should have the numbers 1 through 17 listed in a column. You
want
to
check and make sure every one of those numbers is present. List
any
that
are missing.

Is that what you want?

Assume the range for the numbers is B2:B18

Try this array** formula:

=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$17,0)),ROW( $1:$17)),ROWS($1:1))

Copy down until you get #NUM! errors meaning all missing numbers
have
been
returned.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Lost in Microbiology"

wrote in message
...
I am making a schedule in Excel and have work areas numbered 1
through
17.

My rows list each employee and the columns list the day of the
week, I
want
to check myself that I have all areas covered. Is there a simple
function
that would tell me if I was missing a number in a column (day of
the
month)
and which was missing? Here is an example of what I have set-up:
June
1 2 3 4
Name
Jack Handy 1 2 3 3
Hem Roids 3 4 2 1
Buck Shot 5 6 1 3

Work areas: 1=bench a 4=bench d
2=bench b 5=bench e
3=bench c

Thanks