Hello! I create the timetable for my school using LibreOffice Calc. My school has private-like lessons, i.e. lessons with one student (or sometimes a couple of students) per teacher (and per hour). So, in the spreadsheet I assign for each student the corresponding teacher. Please take a look on the sample file (attachment). The upper part is the timetable that I work on (timetable per student), and the lower part is a “automatic” representation of each teacher’s schedule. “X” denotes that the student is not available on this specific hour (time slot). The schedule of the teachers is “read” from the students timetable with a function (combination of if and countif). What I need: when a teacher is impossible to have lesson for a specific hour, I need an “X” to be displayed in this specific hour on the teacher’s schedule (lower part of the spreadsheet) AUTOMATICALLY. I mean that I need some kind of function in these cells that could read for each one of the teachers, if there is an available student for the specific hour. If there is no student available, I need the cell on the teachers schedule to have an “X”. For example take a look on the teacher “Mary”, Monday 2nd hour. It’s impossible for her to have a lesson because none of her students (neither STUDENT_02, nor STUDENT_09 are available on this specific hour). So, the corresponding cell on the lower part (Monday, 2nd hour for Mary, at the lower part of the spreadsheet, should have an “X”, which should be placed automatically). Is there any way to do it? Thanks a lot!
See this reworked example.
In the TEACHER’s table the row-addressing of ranges was wrongly relative in parts. I rectified that.
I also suggested a possible solution to what you want (as far as I had an understanding) in the attached document.
That solution relies on the LibO Calc function TEXTJOIN which only is available and well working in LibO versions 5.3.3release and higher. The formulae in the helper column need to be entered for array-evaluation.
Simplified the solution to avoid the TEXTJOIN function.
===Edit1 2019-01-01 23:48 CET===
As announced in my recent comment I attach this enhanced demonstration. It uses TEXTJOIN() again and requires LibreOffice vrsion 5.3.3 or higher therefore. (It will not work in AOO.)
===Edit2 2019-01-02 14:27 CET===
This new attachment contains some suggestions concerning the requests from a next comment by the OP.
I checked your file. It works! Thank you! I just need to see carefully (and understand) the combination of functions you used.
My simplified solution is nearly the same as that by SM_Riga.
COUNTIF(ArrayReference;"=" & ValueOrCellReference) or just
COUNTIF(ArrayReference; ValueOrCellReference) both help to find the number of matches. There is a tiny difference, however, for what I prefer the SUMPRODUCT() (despite the silly name).
The more relevant difference was in my original solution where I also produced a list of students per teacher in a helper column. That might be useful sometimes beyond the “eXing-out”. Tell me if you want to see that withdrawn variant again.
By the way: For what reason is teacher JHN listed twice?
The second “appearance” of JHN was my mistake…
I would like to check your other variant. I didn’t really understand how it works (to be honest, I’ve not completely understood how this solution works, but anyway I made the necessary modifications on the formula for my spreadsheet, and it seems that it works just perfectly!). By the way, if one needs to “export” the timetable per teacher is there any “function” to do it? Just imagine that you need to provide John with his own timetable. You need a timetable with the name of each student (for student_01, student_06) placed in the correct cell (time slot).The same applies for the other teachers… But… is there any way to do it? Thanks again! (I think that I should post a new question…).
I will attach another solution where you find the students listed per teacher in an ectra column - just for information without further evaluation.
In addition I enhanced the formulae to put the respective student into the teachers’ timetable. I also allowed for more than one student in a single lesson and demonstrated the case.
Concerning the printing:
If your example is realistic I see no need for specialised prints per teacher.
Otherwise a specific suggestion should be based on the details.
Thank you again! I’m looking forward to checking your new formulas and the functionality of the spreadsheet.
Oh! I got your file! GREAT!!! I’m going to check again your formula tomorrow. To be honest, I can’t understand how it works. I’m sure that you have a really profound knowledge on spreadsheets. Thank you again!
Hello again! Lupp, I checked your formula again. Unfortunately, what I can understand is just that it works. I need to read in detail the manual for each one of the functions you used. This form of the timetable (per teacher, each cell has the “names” of the students) is good for printing. It would be better if the “X” and “-” were missing. Could you send me a variant of the formula which DOES NOT display the “X” and “-” in the teachers timetable?I don’t need this symbols for printing. Thank you!
Wow! I just checked the new file. More than GREAT, and much more than I asked… One trillion thanks!!!
Lupp, a question about the enhanced demonstration file (Edit1 2019-01-01 23:48 CET)…
The cell F41 has the following formula:
=TEXTJOIN(CHAR(10),1,IF(F$7:F$17=$D41,$E$7:$E$17,"")) & IF(CURRENT()="",IF(SUMPRODUCT($D$7:$D$17=$D41,F$7:F$17<>“X”)=0,“X”,"-"),"")
This formula results in the cell F41 to display either the student(s), or “X” or “-”. I need to F41 to display only the student(s) (if there are no students for this time slot and teacher, the cell should remain empty) and never “X” or “-”. This is enough for printing the timetable for the teachers and actually it’s more “clear” for them (most of them are going to ask what does “X” and “-” mean).
I tried to make some modifications on the formula, but I failed, since I haven’t completely understood the syntax (and functionality) of each part of the formula. Could you help me? Thanks!
(The sheets I saw don’t use a cell F41.)
You originally wanted the “X” or “-” respectively to support the planning, I suppose. Therefore I didn’t remove that from the ‘Planning’ sheet, but created the sheets ‘PrintingMany’ and ‘PrintingFewSelected’ in the next demo attached under “Edit2”. There the “supporters” are no longer included.
Generally I would advise not to also use sheets for data keeping or planning for printing. Printing may require formatting and scaling in a way that can be counterproductive otherwise.
Anyway you find the formula you seem to look for in
PrintingFewSelected.F4 of the final example document.
Sorry, I was editing your file (copy → paste) for experimenting, and I used my (edited) file… You are right, there is no cell F41. The formula is this one:
=TEXTJOIN(CHAR(10),1,IF(F$7:F$17=$D23,$E$7:$E$17,"")) & IF(CURRENT()="",IF(SUMPRODUCT($D$7:$D$17=$D23,F$7:F$17<>“X”)=0,“X”,"-"),"")
is in your other file:
(this is the second one you uploaded here) and it’s the cell F23. This file has the simplest approach for me. If you could just modify this formula (the above one) so it doesn’t display the “X” and “-” and just keep the student(s) name(s), it’s more than enough for the moment. Actually it would be a very good solution for me (and not only for printing…). Thanks!
Lupp, after watching some tutorials on YouTube for spreadsheets, I realized that my mistake was that I should use a special keystroke for entering the formula correctly! So, the formula now works exactly as I need it!
Sorry! I should have told about “CSE”. This would have saved you the time you wasted with these annoyng videos often giving explanations “mumbled” in a repulsive way.
Another thing: If you can assure there are no lessons one teacher gives for more than one student, you don’t need the TEXTJOIN() function anyway. Then you also won’t need to force array-evaluation with Ctrl+Shift+Enter, and to use Ctrl in addition when drag-filling the formulae.
I personally would prefer a TEXTJOIN() not allowing for optional parameters, and having specified the only remaining parameter introducing elements then as ForceArray (like MATCH() e.g. in its second place). But the function is shaped after Excel 2016,… (Alas!)
One point more: My suggestion to strictly separate data keeping and planning from printing is good advice - as you will find out in the long run. Having set formats where you need to maintain formulae and to fill them elsewhere causes avoidable pain again and again.
Thank you Lupp! Your approach for two (or more students) per teacher (and per hour) is fine. Actually, this is what happens at my school. As I mentioned, the lessons are private-like. Some teachers, instead of teaching to one student (which be should be the normal case), they teach to a group of two (or more students), since the Ministry of Education does not hire enough teachers.
Are you preparing timetables as well? You seem to be really experienced. Of course, you are not obliged to answer my question!
My first aim is to prepare the timetable and make the modification procedure as easy and “safe” as possible. The printing part, is not the crucial problem for the moment. I “suspect” that the utilization of a data-base system might make the procedure more flexible. And of course, there are approaches that facilitate the whole procedure (keeping data, preparing the timetable, printing it). But, I’m not as experienced as you are.
I thought I already told that I made (big part of) the assignment of “resources” (teachers and rooms mainly) to the needed lessons of teaching. The lessons were never private-like but for classes. The school was up to about 100 teachers for 1150 students. The final scheduling only was supervised by me. Special software (applied graph-theory) was used for it.
No. Databases are important, and for some tasks they are simply necessary (unavoidable). I never found one “flexible”. In fact I dislike them. No good support for creative work. “Deep Learning” software may change that.
Your task reminds me of planning needed in Germany in schools of basic type “Musisches Gymnasium”. There are variants.
Please find an example spreadsheet attached.
The idea is to add one more test with COUNTIFS function to count if there is any cell with value that is not equal to X and with the teacher code that is equal to current teacher. For example Mary + Monday + Hour Two returns 0, so no students are available.
Your solution works as well! Thank you so much!