Checking for specific entries in a column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frador - 09 May 2008 16:54 GMT

How can I make it so Excel checks a column to see if the are an exact set of
entries in a column, in no particular order, and with empty cells. I'm doing
a schedule, and none of the templates I've found work for me. Each column
represents either a day or night shift, and I need to be sure I've scheduled
enough people. I have to enter a variety of jobs and starting times with no
set ending times. I need a formula that will let me know if I've got all the
staff I need. Any ideas? I'm thinking it's some sort of combination of "and",
"count" and "countif", but I can's quite figure it out.

Reply to this Message

KC Rippstein hotmail com> <kcrippstein - 09 May 2008 22:46 GMT

I think you need to be more specific in what you're after, but I have a
suggestion.

Instead of having one daytime shifts column and one nightime shifts column
and trying to figure out if you have enough staff for each hour of the day,
you might separate this instead into start time and end time.

Then you could have a summary at the bottom of the day that has each hour
(7:00 AM in cell A50, 8:00 AM in cell A51, etc.) and use the following in B50
(I'll assume start time is in column B and end time in C):
=sumproduct(($B$2:$B$49<=$A50)*($C$2:$C$49>$A50))

Hope that helps.

Signature

Please remember to indicate when the post is answered so others can benefit
from it later.

> How can I make it so Excel checks a column to see if the are an exact set of
> entries in a column, in no particular order, and with empty cells. I'm doing
[quoted text clipped - 4 lines]
> staff I need. Any ideas? I'm thinking it's some sort of combination of "and",
> "count" and "countif", but I can's quite figure it out.

Reply to this Message

Frador - 09 May 2008 23:12 GMT

There are precise start times, but no precise end times. It's a restaurant,
so I have to schedule servers, bartenders, etc, and many of them start at
different times. There's no set end of shift time. It's based on how busy the
restaurant is. I just have to be sure that I have covered all the shifts. On
the weekends, it's a lot of people, and I often leave some out.

"KC Rippstein" wrote:

> I think you need to be more specific in what you're after, but I have a
> suggestion.
[quoted text clipped - 18 lines]
> > staff I need. Any ideas? I'm thinking it's some sort of combination of "and",
> > "count" and "countif", but I can's quite figure it out.

Reply to this Message

KC Rippstein hotmail com> <kcrippstein - 17 May 2008 02:47 GMT

Sorry it took so long to get back to you again. My PC at work died on me.

I'm still not sure I get what you're after, so if you'd like me to offer a
little more help, feel free to email your file to me at
kcrippstein<at>hotmail<dot>com. I'd be happy to help.

Signature

Please remember to indicate when the post is answered so others can benefit
from it later.

> There are precise start times, but no precise end times. It's a restaurant,
> so I have to schedule servers, bartenders, etc, and many of them start at
[quoted text clipped - 26 lines]
> > > staff I need. Any ideas? I'm thinking it's some sort of combination of "and",
> > > "count" and "countif", but I can's quite figure it out.

Reply to this Message