Error Checking

  • Thread starter Lost in Microbiology
  • Start date
L

Lost in Microbiology

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
 
T

T. Valko

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
 
T

T. Valko

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

Should be:

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

Biff
 
L

Lost in Microbiology

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

T. Valko

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
 
L

Lost in Microbiology

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

T. Valko

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
 
L

Lost in Microbiology

Bloody brilliant, thanks so much!

T. Valko said:
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
Click to expand...
 
Top