If you want worksheet formulas, better to post this in Misc or
Worksheet.Functions

You need to set you clock back to the right time. You are several hours
ahead of everyone else.

--
Regards,
Tom Ogilvy

"J_J" wrote in message
...
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

John Walkenbach has sample code that shows how to fill a listbox with unique
items in a list. You can easily adapt this method of using a collection to
identify the unique items and even the sort code if you want them sorted.

http://j-walk.com/ss/excel/tips/tip47.htm
Filling a ListBox With Unique Items

rather than extract the information from the collection and put it in a
listbox, you would just put it in the appropriate cells. For getting
counts, you can use the Countif formula. If you want to count the classes

=countif(G10:K19,G24&"*")

You layout seems a bit suspect though. If you have unique lessons in column
G, unless each lesson is given to only one class, it is unclear how entries
would be made in H, but that is the advantage of giving you the tools since
you know exactly what you want.



--
Regards,
Tom Ogilvy


"J_J" wrote in message
...
Thank you Tom, I' ll do as you've suggested.
But I said I 'll be happy to see macro solutions as well...
Sincerely
J_J

"Tom Ogilvy" wrote in message
...
If you want worksheet formulas, better to post this in Misc or
Worksheet.Functions

You need to set you clock back to the right time. You are several hours
ahead of everyone else.

--
Regards,
Tom Ogilvy

"J_J" wrote in message
...
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

Hi Jack,
If you had fixed your clock like Tom suggested your reply and your other posting would have had the correct time.
You must correct your Time Zone before setting the time.

Settings, Control Panel, Date/TIme

and then to set your computer clock, which is easily set
within the nearest second by installing a program and
invoking it online.

Set Your Computer Clock Via the Internet
NIST Internet Time Service (ITS)
http://www.boulder.nist.gov/timefreq/service/its.htm
look for the link on the right
Windows 95 and later (32 or 64-bit)

This weekend clocks go back to standard time, as far as
the program above is concerned, time will be changed based
on Universal Time rather than 1 AM of your local time if you
happen to run the program about that time.

Also please use your spell checker. Pretty please.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"J_J" wrote in message ...
Thank you Tom, I' ll do as you've suggested.

You can get atomic clock here and make it automatic when booting pc. I love
it.
http://www.worldtimeserver.com/

--
Don Guillett
SalesAid Software

"David McRitchie" wrote in message
...
Hi Jack,
If you had fixed your clock like Tom suggested your reply and your other
posting would have had the correct time.
You must correct your Time Zone before setting the time.

Settings, Control Panel, Date/TIme

and then to set your computer clock, which is easily set
within the nearest second by installing a program and
invoking it online.

Set Your Computer Clock Via the Internet
NIST Internet Time Service (ITS)
http://www.boulder.nist.gov/timefreq/service/its.htm
look for the link on the right
Windows 95 and later (32 or 64-bit)

This weekend clocks go back to standard time, as far as
the program above is concerned, time will be changed based
on Universal Time rather than 1 AM of your local time if you
happen to run the program about that time.

Also please use your spell checker. Pretty please.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"J_J" wrote in message
...
Thank you Tom, I' ll do as you've suggested.

Hi,
Hope my time settings are OK now.
I am sorry for all the trouble I've introduced because of me.
Now I hope I can get some feedback on my Q.
TIA
J_J

"David McRitchie" wrote in message
...
Hi Jack,
If you had fixed your clock like Tom suggested your reply and your other
posting would have had the correct time.
You must correct your Time Zone before setting the time.

Settings, Control Panel, Date/TIme

and then to set your computer clock, which is easily set
within the nearest second by installing a program and
invoking it online.

Set Your Computer Clock Via the Internet
NIST Internet Time Service (ITS)
http://www.boulder.nist.gov/timefreq/service/its.htm
look for the link on the right
Windows 95 and later (32 or 64-bit)

This weekend clocks go back to standard time, as far as
the program above is concerned, time will be changed based
on Universal Time rather than 1 AM of your local time if you
happen to run the program about that time.

Also please use your spell checker. Pretty please.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"J_J" wrote in message
...
Thank you Tom, I' ll do as you've suggested.

Hi
yes, now the time is OK :-)))

--
Regards
Frank Kabel
Frankfurt, Germany


J_J wrote:
Hi,
Hope my time settings are OK now.
I am sorry for all the trouble I've introduced because of me.
Now I hope I can get some feedback on my Q.
TIA
J_J

"David McRitchie" wrote in message
...
Hi Jack,
If you had fixed your clock like Tom suggested your reply and your
other
posting would have had the correct time.
You must correct your Time Zone before setting the time.

Settings, Control Panel, Date/TIme

and then to set your computer clock, which is easily set
within the nearest second by installing a program and
invoking it online.

Set Your Computer Clock Via the Internet
NIST Internet Time Service (ITS)
http://www.boulder.nist.gov/timefreq/service/its.htm
look for the link on the right
Windows 95 and later (32 or 64-bit)

This weekend clocks go back to standard time, as far as
the program above is concerned, time will be changed based
on Universal Time rather than 1 AM of your local time if you
happen to run the program about that time.

Also please use your spell checker. Pretty please.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"J_J" wrote in message
...
Thank you Tom, I' ll do as you've suggested.

Sorry Frank. Sorry everyone.
I was forgetting to correct my time zone first...
J_J

"Frank Kabel" wrote in message
...
Hi
yes, now the time is OK :-)))

--
Regards
Frank Kabel
Frankfurt, Germany

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

Thank you Tom, I' ll do as you've suggested.
But I said I 'll be happy to see macro solutions as well...
Sincerely
J_J

"Tom Ogilvy" wrote in message
...
If you want worksheet formulas, better to post this in Misc or
Worksheet.Functions

You need to set you clock back to the right time. You are several hours
ahead of everyone else.

--
Regards,
Tom Ogilvy

"J_J" wrote in message
...
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