Counting the number of Alphabets.

[inayat]

Hi All,

A little background. This sheet is for a learning center wherein we give short courses to different candidates. Now we have a sheet "MAster Sheet" wherein we have the master data. If a particular candidate is taking a class in that week we mark it with "x". the teacher that gives the lecture is marked as "y". If it is a half day for teacher then we mark as "h". So let us say if Student 1 takes the class with Teacher 1, the cell will be like "xy" and if student 2 takes two classes in the week from Teacher 2 the will be marked as "xyxy". Now we count the x's for the invoicing and y's and h's for payroll as the teachers are paid on the basis of no. of classes given.

Now I have made the two sheets. Since invoicing is a little automated we just need to put in the number of classes and the course. So i have taken care of that in the Student Sheet. Now my problem is in Teachers sheet. Below is given what I want to do in it:

In the column Teacher I want to have the names of all the teachers. Now the names of some of the teachers will be repeated as they may teach more than 1 student a month. If that is the case I want the name of the teacher to appear as many times as the rates of the classes may be different. However, the teacher column should be sorted so that if Teacher 1 has given 3 classes it should come one after another.

In the column Service, I want to have the name of the course that the teacher has given which is a multiple of Zone and Min in the Master Sheet.

In the column No. of Classes I want to have the total no. of classes that teacher has given for each line. However, if it is y than it has to count as 1 if it is h than it has to count as 0.5. Example xyxh will be 1.5

In the column price, I want to have the price for each class. This one I have done using vlookup from the price sheet.

Also, please check the students sheet. I have used the formula LEN and Substitute to count the number of classes. I am sure there will be an easier formula for the same.

Please let me know if you need any clarifications. And sorry for such a long explanation.

Cheers-
Inayat

P.S: Moderators: I really didnt know what to write as a title. If it is not relevant please suggest one and I will change it asap.

[dilipandey]

okay.. and what is the role of H1:L1 of Master sheet ?



Regards,
DILIPandey
<click on below * if this helps>

[inayat]

Hi Dilip,

H1:L1 is are the columns from where I take the no. of classes. Each cell is basically a week. X represents the no. of classes a student had taken in a particular week. Y represents that a teacher has taken the class.

[dilipandey]

In order to calculate Ys and Hs, you can use below formula:-

Please Login or Register  to view this content.

entered with key combination: ctrl shift enter


see attachment:- counting character in a text string.xlsx


Regards,
DILIPandey
<click on below * if this helps>