summerizing formula for weekly lesson plans

[Myrna Larson]

Your layout is not optimal for the kind of analysis you describe.

I think you need to create a 2-column x 46 row list from the G10:K19 area.
The first row would contain the headers Lesson and Class. The 2nd-46th rows
would give lesson and class name for each of the cells in G10:K19. From that
point, you can use a Pivot Table to get your counts.

It may be easy for a human to separate the lesson name and class name, but
that isn't true for Excel. On Oct 20 I posted a function to separate a
string into separate words using the criterion that a word begins with a
capital letter. Maybe that will be of help.

 

[J_J]

Hi Myrna,
Thanks for your comments. I managed to accomplish all I've asked for with
some little tricks. I added a "space" between the lesson and class names.
Thus now I can use the criteria of "find the space char and take the right
of this".
Only one problem remains. I'd appreciate some help on this remaining issue.
How can I hide the rows if the corresponding cell in column H:H has a zero
in it?
J_J

 

[J_J]

sorry for the mistype

" How can I hide the rows if the corresponding cell in column H:H has a zero
in it?"

should have been

"How can I hide the rows if the corresponding cell in column H:H has only a
zero number in it?"
Regards
J_J

 

[Myrna Larson]

You can use AutoFilter with the custom criterion <>0

 

[David McRitchie]

Hi Jack,
For additional information on filtering, you might take a look at
Debra Dalgleish's pages on AutoFilter
at http://www.contextures.com/tiptech.html

 

[J_J]

Thnks Myrna, I now that. I am looking for an alternative...
Regards

 

[J_J]

Hi Jack,
For additional information on filtering, you might take a look at
Debra Dalgleish's pages on AutoFilter
at http://www.contextures.com/tiptech.html

 

[J_J]

Thanks David. I'll do that.

 

[Max]

"How can I hide the rows if the corresponding cell
in column H:H has only a zero number in it?"

Just as an alternative, you may want
to try something along these lines ..

Assume the table is in Sheet1, cols A to H(?),
headers in row1, data from row2 down

In an empty col to the right of the table, say col K

Put in K2: =IF(OR(H2="",H2=0),"",ROW())

Copy down by a safe # of rows to accomodate expected data in the table

In a new Sheet2
-----------------------
With the same headers of Sheet1 in row1,

Put in A2:
=IF(ISERROR(MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$K:$K,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$K:$K,0)-1,COLUMN(A1)-1))

Copy A2 across to H2, fill down by as many rows
as was done for col K in Sheet1

Sheet2 will auto-return the "filtered" rows from Sheet1
where col H in Sheet1 is neither blank nor contain zeros

 

[J_J]

Hi,
This is a request from a primary school headteacher and a good friend of
mine.
They use an excel sheet that shows the lesson plan for each teacher. Nothing
special on it. But lately they needed some summerizing figures on the
sheets.
For each teacher, there are "names" in the region G10:K19 in its excel
sheet. The names includes a part consisting of the "lesson name" itself and
a part consisting the "class" that'l take it (For example "ScienceA1"). Thus
the "name" data can easily be seperated because the "lesson name" is at the
beginning and the "class" name always starts with a capital letter somewhere
in the middle of the text.
Now I wanna summerize this data for each teacher down somewhere on the sheet
as follow:
I want to list these lessons in the region from F24:I24 downwards such that:
F24,F25,F26,...etc will be 1, 2, 3, ...etc the number of times a unique name
is found in the region G10:K19.
G24, G25, G26, ....etc will be each found different lesson name.
H24, H25, H26, ...etc will be the involved class for that lesson (e.g A1, B2
etc).
I24, I25, I26, ...etc will be the total count of each unique lesson in the
region G10:K19.
Can you suggest formulas or a macro to do it?
Thanks in advance
J_J

 

[J_J]

Thank you for your suggestions Max.
I think I' ll use a small macro to accomplish a filtering on open-up.
Regards
J_J

 

[Max]

Thank you for your suggestions Max.
I think I' ll use a small macro to accomplish a filtering on open-up.
Regards
J_J

You're welcome !
Do go as preferred ..