countif / sumproduct or something else?

[MJKelly]

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

 

[Joel]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Bob Phillips]

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)

 

[MJKelly]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[MJKelly]

Hi Sandy,

I have 200 duties, one on each row. Each duty is broken down into ten
minutes time slots so I know the task they are performing in each ten
minutes (as the tasks change during the duty). Their are 30 different
tasks which can be performed. So, originally each day of the week had
a worksheet and each duty which occurred on that day was included. A
simple Countif was used to count all instances of each task in the
first time slot for the 200 duties, then the next summary cell counted
the second time slot (again instances of the different tasks). I used
a summary table for each day of the week. But, now I want to use one
worksheet to contain the duties. Each row still represents one duty,
but if the duty occurs on Monday or Tuesday etc then a "Y" is entered
against that duty for the corresponding day column. How would I now
produce a summary which checks to see if the duty occurs on a given
day, and how many times each task occurs in the time slot for the
total 200 duties. Again I would have a summary table for each day and
a total table to show total amount for each task for the week (again
in time slots).

Like I say it works if the duties are input into different worksheets
(one for each day), but for maintaining the dutyset, it would be
easier to just be able to state that a duty has these tasks in these
time slots and the duty occurs on these days.

Hope I've made sense,
kind regards,
Matt

 

[Sandy Mann]

Right, first of all my apologies for being so thick. Can I assume that Duty
1 say, which you give on Monday as being:

No. 06:00 06:10 06:20 06:30 06:40 06:50
1 PA BM BM BM BM BM

would be exactly the same if performed on Tuesday or Wednesday?

If so then you could have a table of all the duties and use it as a lookup
table then use a VLOOKUP() formula to return the Task.

In other words:

If I use the the data the you supplied for Monday as a table in Sheet3 A1 to
G9
:
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

then in the Summary sheet with headers in A1:H1:
Duty Monday, Tuesday etc.

and then in J1:N1
06:00 06:10 06:20 06:30 06:40 06:50


Finally enter the following formula in I2:

=IF(COUNTA($A2:$D2)>1,VLOOKUP($A2,Sheet3!$A$2:$G$9,COLUMN()-COLUMN($G$1),FALSE),"")

and copy using the fill handle across to N2 and then down to N9

Now if you enter a Duty number in Column A and Y in any day Column then the
tasks for that duty will display in the appropriate cells.

If the Duties are not exactly the same regardless of what day they are
performed how can you display the same Duty number doing different Tasks on
different days?

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[MJKelly]

Sandy sorted this out with sumproduct. I'll also try the possible
solution above too (again form Sandy), to see if it works or to see
how I might use this method in the future.

many thanks,
Matt