Hi,
I have a table detailing staff duties in 10 minute blocks. I use one
sheet for each day of the week, and have included a summary worksheet
which uses a countif function to total the amount of duties performing
a certain task in a given 10 minute block.

this (extract) is the 10 minute data for Monday.

No. 06:00 06:10 06:20 06:30 06:40 06:50
1 PA BM BM BM BM BM
2 BM BM BM BM BM BM
3 MHE MHE MHE MHE MHE MHE
4 MHE MHE MHE MHE MHE MHE
5 PM PM PM PM PM PM
6 PM PM PM PM PM PM
7 MHE MHE MHE MHE MHE MHE
8 MHE MHE MHE MHE MHE MHE


This (extract) is the summary count for MONDAY
06:00 06:10 06:20 06:30
PA 1 0 0 0
PM 42 42 42 42
MHE 10 10 10 10
XD 0 0 0 0
BM 1 2 2 2
GH 2 2 2 2
AT 1 1 1 1


My question is - I want to reduce the seven daily worksheets to one
sheet and add seven columns to determine if a duty occurs on a given
day. See below. How would I then produce a summary worksheet to
total the tasks in 10 minute blocks providing the task occurs on a
Monday, Tuesday etc? I thought of sumproduct or a countif with two
arguments? It works so well with seven seperate worksheets, but for
maintainence it would be better to use one worksheet. I think a macro
which loops through every cell in the range (30,000 plus cells), would
take too long as it would be updated regularly. Any thoughts?

Duty M Tu W 06:00 06:10
1 Y Y Y PA BM
2 Y Y BM BM
3 Y Y Y MHE MHE
4 Y Y Y MHE MHE
5 Y Y Y PM PM
6 Y PM PM
7 Y Y Y MHE MHE
8 Y Y Y MHE MHE

Worksheet functions are less efficient and run slower than VBA code.
Worksheet functions will slow down the worksheet because it has recaculate
after every entry.

I put numbers in the worksheet 100 columns wide and 300 rows long to get
30,000 data points. I ran the code below which took about 1 to 2 seconds to
run

Sub count()

Dim Total As Long

StartTime = Now()
Total = 0
For RowCount = 1 To 300
For ColCount = 1 To 100

Total = Total + Cells(RowCount, ColCount)
Next ColCount
Next RowCount
EndTime = Now()
MsgBox (Format(EndTime - StartTime, "SS"))

End Sub


"MJKelly" wrote:


Hi,
I have a table detailing staff duties in 10 minute blocks. I use one
sheet for each day of the week, and have included a summary worksheet
which uses a countif function to total the amount of duties performing
a certain task in a given 10 minute block.

this (extract) is the 10 minute data for Monday.

No. 06:00 06:10 06:20 06:30 06:40 06:50
1 PA BM BM BM BM BM
2 BM BM BM BM BM BM
3 MHE MHE MHE MHE MHE MHE
4 MHE MHE MHE MHE MHE MHE
5 PM PM PM PM PM PM
6 PM PM PM PM PM PM
7 MHE MHE MHE MHE MHE MHE
8 MHE MHE MHE MHE MHE MHE


This (extract) is the summary count for MONDAY
06:00 06:10 06:20 06:30
PA 1 0 0 0
PM 42 42 42 42
MHE 10 10 10 10
XD 0 0 0 0
BM 1 2 2 2
GH 2 2 2 2
AT 1 1 1 1


My question is - I want to reduce the seven daily worksheets to one
sheet and add seven columns to determine if a duty occurs on a given
day. See below. How would I then produce a summary worksheet to
total the tasks in 10 minute blocks providing the task occurs on a
Monday, Tuesday etc? I thought of sumproduct or a countif with two
arguments? It works so well with seven seperate worksheets, but for
maintainence it would be better to use one worksheet. I think a macro
which loops through every cell in the range (30,000 plus cells), would
take too long as it would be updated regularly. Any thoughts?

Duty M Tu W 06:00 06:10
1 Y Y Y PA BM
2 Y Y BM BM
3 Y Y Y MHE MHE
4 Y Y Y MHE MHE
5 Y Y Y PM PM
6 Y PM PM
7 Y Y Y MHE MHE
8 Y Y Y MHE MHE

Worksheet functions are less efficient and run slower than VBA code.
Worksheet functions will slow down the worksheet because it has recaculate
after every entry.

Not true. Worksheet functions are MUCH faster than VBA and will only
calculate the other cells that are dependant on the changed cell unless
there are more than 65,536 unique dependant references.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Joel" wrote in message
...
Worksheet functions are less efficient and run slower than VBA code.
Worksheet functions will slow down the worksheet because it has recaculate
after every entry.

I put numbers in the worksheet 100 columns wide and 300 rows long to get
30,000 data points. I ran the code below which took about 1 to 2 seconds
to
run

Sub count()

Dim Total As Long

StartTime = Now()
Total = 0
For RowCount = 1 To 300
For ColCount = 1 To 100

Total = Total + Cells(RowCount, ColCount)
Next ColCount
Next RowCount
EndTime = Now()
MsgBox (Format(EndTime - StartTime, "SS"))

End Sub


"MJKelly" wrote:


Hi,
I have a table detailing staff duties in 10 minute blocks. I use one
sheet for each day of the week, and have included a summary worksheet
which uses a countif function to total the amount of duties performing
a certain task in a given 10 minute block.

this (extract) is the 10 minute data for Monday.

No. 06:00 06:10 06:20 06:30 06:40 06:50
1 PA BM BM BM BM BM
2 BM BM BM BM BM BM
3 MHE MHE MHE MHE MHE MHE
4 MHE MHE MHE MHE MHE MHE
5 PM PM PM PM PM PM
6 PM PM PM PM PM PM
7 MHE MHE MHE MHE MHE MHE
8 MHE MHE MHE MHE MHE MHE


This (extract) is the summary count for MONDAY
06:00 06:10 06:20 06:30
PA 1 0 0 0
PM 42 42 42 42
MHE 10 10 10 10
XD 0 0 0 0
BM 1 2 2 2
GH 2 2 2 2
AT 1 1 1 1


My question is - I want to reduce the seven daily worksheets to one
sheet and add seven columns to determine if a duty occurs on a given
day. See below. How would I then produce a summary worksheet to
total the tasks in 10 minute blocks providing the task occurs on a
Monday, Tuesday etc? I thought of sumproduct or a countif with two
arguments? It works so well with seven seperate worksheets, but for
maintainence it would be better to use one worksheet. I think a macro
which loops through every cell in the range (30,000 plus cells), would
take too long as it would be updated regularly. Any thoughts?

Duty M Tu W 06:00 06:10
1 Y Y Y PA BM
2 Y Y BM BM
3 Y Y Y MHE MHE
4 Y Y Y MHE MHE
5 Y Y Y PM PM
6 Y PM PM
7 Y Y Y MHE MHE
8 Y Y Y MHE MHE

You are correct in principle Sandy, that is an amazing statement by Joel,
but often VBA can be quicker than a whole raft of worksheet functions, if
intelligently designed. The VBA will not be quicker than a single worksheet
function, but by doing less the net effect can be beneficial.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sandy Mann" wrote in message
...
Worksheet functions are less efficient and run slower than VBA code.
Worksheet functions will slow down the worksheet because it has
recaculate
after every entry.

Not true. Worksheet functions are MUCH faster than VBA and will only
calculate the other cells that are dependant on the changed cell unless
there are more than 65,536 unique dependant references.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Joel" wrote in message
...
Worksheet functions are less efficient and run slower than VBA code.
Worksheet functions will slow down the worksheet because it has
recaculate
after every entry.

I put numbers in the worksheet 100 columns wide and 300 rows long to get
30,000 data points. I ran the code below which took about 1 to 2 seconds
to
run

Sub count()

Dim Total As Long

StartTime = Now()
Total = 0
For RowCount = 1 To 300
For ColCount = 1 To 100

Total = Total + Cells(RowCount, ColCount)
Next ColCount
Next RowCount
EndTime = Now()
MsgBox (Format(EndTime - StartTime, "SS"))

End Sub


"MJKelly" wrote:


Hi,
I have a table detailing staff duties in 10 minute blocks. I use one
sheet for each day of the week, and have included a summary worksheet
which uses a countif function to total the amount of duties performing
a certain task in a given 10 minute block.

this (extract) is the 10 minute data for Monday.

No. 06:00 06:10 06:20 06:30 06:40 06:50
1 PA BM BM BM BM BM
2 BM BM BM BM BM BM
3 MHE MHE MHE MHE MHE MHE
4 MHE MHE MHE MHE MHE MHE
5 PM PM PM PM PM PM
6 PM PM PM PM PM PM
7 MHE MHE MHE MHE MHE MHE
8 MHE MHE MHE MHE MHE MHE


This (extract) is the summary count for MONDAY
06:00 06:10 06:20 06:30
PA 1 0 0 0
PM 42 42 42 42
MHE 10 10 10 10
XD 0 0 0 0
BM 1 2 2 2
GH 2 2 2 2
AT 1 1 1 1


My question is - I want to reduce the seven daily worksheets to one
sheet and add seven columns to determine if a duty occurs on a given
day. See below. How would I then produce a summary worksheet to
total the tasks in 10 minute blocks providing the task occurs on a
Monday, Tuesday etc? I thought of sumproduct or a countif with two
arguments? It works so well with seven seperate worksheets, but for
maintainence it would be better to use one worksheet. I think a macro
which loops through every cell in the range (30,000 plus cells), would
take too long as it would be updated regularly. Any thoughts?

Duty M Tu W 06:00 06:10
1 Y Y Y PA BM
2 Y Y BM BM
3 Y Y Y MHE MHE
4 Y Y Y MHE MHE
5 Y Y Y PM PM
6 Y PM PM
7 Y Y Y MHE MHE
8 Y Y Y MHE MHE

Thanks, for the discussion, very interesting. I'm not sure that the
code above solves my problem though. How can I count each occurance
of task A in the timeslot 06:00 to 06:10 for 200 rows of data, only if
the duty occurs on a Monday and then place the result in a summary
sheet, then move onto the next time slot, and after all timeslots have
been queried, query the same data for Task B, after that, I need to do
it all again but ask if Tuesday is a day on which the duty occurs etc
etc. Each row represents a duty and its content (tasks split to
minute blocks of time), the row also details the days on which the
duty occurs.

Like I say a COUNTIF works, but when I used this I have seven
worksheets (one for each day of the week), now I only have the one
worksheet and state for each duty the days the duty occurs.

fingers are crossed,
kind regards,
Matt

Hi Matt,

Speaking for myself, I do not understand your sample data:

In the first exteact I assume that the numbers are table numbers?

In the second extract I dn't understand why PM has 42 and MHE has 10. What
do these numbers represent?

In the third extract you have M Tu W and times. Do you want to know only if
a person works that day or the time that they worked?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"MJKelly" wrote in message
...
Thanks, for the discussion, very interesting. I'm not sure that the
code above solves my problem though. How can I count each occurance
of task A in the timeslot 06:00 to 06:10 for 200 rows of data, only if
the duty occurs on a Monday and then place the result in a summary
sheet, then move onto the next time slot, and after all timeslots have
been queried, query the same data for Task B, after that, I need to do
it all again but ask if Tuesday is a day on which the duty occurs etc
etc. Each row represents a duty and its content (tasks split to
minute blocks of time), the row also details the days on which the
duty occurs.

Like I say a COUNTIF works, but when I used this I have seven
worksheets (one for each day of the week), now I only have the one
worksheet and state for each duty the days the duty occurs.

fingers are crossed,
kind regards,
Matt