Can this Be Done in Excel ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jmacvicar@autranscanada.com - 15 Oct 2007 19:43 GMT

I have a employee job scheduling table that I would like to populate
based on criteria from 2 other tables.

The scheduling table has the employee names listed in each row and
across the top is the period of the day broken into 4 time periods.

The critera tables are:

1. A training matrix that lists the employee names in each row and
the
jobs listed across the top. If an employee is trained, the
intersecting cell has a "T". If they are not trained, the cell is
blank.

2. An attendance table that lists the employee names in each row and
if they are absent, there is an "A" in the next cell. If they are at
work, the cell is blank.

I'd like to be able to populate the scheduling table based on whether
they are trained and present at work. Basically, each day the
attendance table would be updated and then the scheduling table would
be automatically re-calculated. There would need to be provisions to
prevent the same employee from being scheduled on the same job more
than once a day on a certain job unless no one else was trained.

Can this be setup in excel ?

Thanks

Jeff

Reply to this Message

Billy Liddel - 15 Oct 2007 23:34 GMT

Jeff

You need someone to write a macro for you, if I could program Sudoku then
maybe I could do this. In the mean time here is something to help you.

I had my table of jobs and employees on one sheet.

Table 1
Names    Job1    Job2    Job3    Job4
Jon        T        T
Joe    T    T        T
Ali            T    T
Jean        T    T
Fred    T    T        T
Alice    T

Table 2
Names    In
Jon    A
Joe
Ali
Jean    A
Fred
Alice

TAble 3 on sheet2

Schedule1    Job1    Job2    Job3    Job4    Tasks Avail
Alice    1    0    0    0    1
Ali    0    0    1    1    2
Joe    1    1    0    1    3
Fred    1    1    0    1    3
Jon    0    0    0    0
Jean    0    0    0    0
Total1    3    2    1    3

the formula in B1 Job1 (Alice)
=SUMPRODUCT(--(Sheet1!$A$3:$A$8=$A2),(Sheet1!B$3:B$8="T")*(Sheet1!$B$12:$B$17<>"A"))

copy this across and down

formula in F1
=IF(SUM(B2:E2)=0,"",SUM(B2:E2))
and copy down

I then sorted the list on column F and used this to produce a schedule below.

I started the schedule in A11 and used the formula =A1 for the names which
helps when sorting.

Will this do for now?

Peter

Reply to this Message

jmacvicar@autranscanada.com - 16 Oct 2007 13:11 GMT

On Oct 15, 6:34 pm, Billy Liddel
<BillyLid...@discussions.microsoft.com> wrote:

> Jeff
>
[quoted text clipped - 49 lines]
>
> Peter

Hi Peter,

This kind of works. However, the format of the schedule I'd like to
end up with is different. Here's what I have:

Table 1 - Training Matrix (Employees in Col A and Job Names in row 3)
A "Y" means that can work that job.

Job 1 Job 2 Job 3 Job 4 Job 5
Phil Y Y Y Y
Bill Y Y Y Y
Joe Y Y Y Y
Dan Y Y Y Y Y
Tim Y Y Y Y

Table 2 - Attendance - An "A" means they are absent & cannot be
scheduled

Phil
Bill
Joe A
Dan
Tim

Table 3 - Schedule

7am-9am 9am-11am 11am-1pm 1pm-3pm
Phil Job 1 Job 2 Job 4 Job 5
Bill Job 3 Job 4 Job 5 Job 1
Joe
Dan Job 2 Job 3 Job 1 Job 4
Tim Job 5 Job 1 Job 3 Job 2

Thanks for your help.

Jeff

Reply to this Message

jaxstraww - 25 Oct 2007 18:01 GMT

Billy,

Can I get the macro for the sudoku solver? I have one puzzle unable to get
and I think the solver would come in handy. Thanks.

> Jeff
>
[quoted text clipped - 49 lines]
>
> Peter

Reply to this Message

ilia - 25 Oct 2007 20:56 GMT

I have a macro for sudoku solver, but it uses a recursive guess-and-
check strategy, not actually "solving" it.

If you want it, let me know.

Billy - how does yours work?

On Oct 25, 1:01 pm, jaxstraww <jaxstr...@discussions.microsoft.com>
wrote:

> Billy,
>
[quoted text clipped - 56 lines]
>
> - Show quoted text -

Reply to this Message

jaxstraww - 26 Oct 2007 02:15 GMT

Sounds like a plan. Still would like a solver though. My addy should be in
the profile. Thanks.

Do I need a puzzle template to put the known numbers in?

> I have a macro for sudoku solver, but it uses a recursive guess-and-
> check strategy, not actually "solving" it.
[quoted text clipped - 65 lines]
> >
> > - Show quoted text -

Reply to this Message

Ilia - 26 Oct 2007 18:52 GMT

I can't find it. Why don't you e-mail me and I'll write back.

> Sounds like a plan. Still would like a solver though. My addy should be in
> the profile. Thanks.
[quoted text clipped - 70 lines]
> > >
> > > - Show quoted text -

Reply to this Message