this is the first time I write here, I was encouraged when I found a problem that I don't know where to start the search. So here I am, it seems to be a place that brings together many brilliant minds.
I work at a university and have been controlling the matrices of courses with their disciplines in google spreadsheets, where I have advanced knowledge to perform data crossings.
Altogether we have about 60 courses, and each course has a structure with 6, 8 or 10 semesters. Each semester in turn has about 6 subjects.
In an adaptation to the new model developed by the academic board, we will offer courses on a monthly basis, and to make the offer, the method uses codes with letters that define the order of the subjects in the subject courses, and numbers that define which semester it belongs. Therefore, A1 (first subject of the first semester), or even C8 (third subject of the eighth semester) and so on.
And to move forward with this need we built a table that illustrates the evolution of this proposal, you can see it here https://docs.google.com/spreadsheets/d/1Xd3YTZlgUeXvk8Pblaks-PhhQ80dBJeMiOC2YtbhraQ/edit#gid=1357433468. Note that the subjects (letters) are fixed in the months. And it is from there that the need to apply a positioning optimization arises.
For this survey, even knowing there may be tools suitable for this purpose, I decided to go as far as I can with the google spreadsheets.
See in the image of this link https://docs.google.com/spreadsheets/d/1_skXPTPzqZtus8KbX9smNYQol7PJK5B1Ad-9HbXDaPk/edit#gid=1408966281 that in each course, I am placing a new column that refers to what I mentioned above, that is, I put the discipline code in the column following the semester rules (which can be seen in column A).
With this done in all the courses' matrices, I made a single spreadsheet that imported all the courses' tabs and from it I start to unroll steps to know which codes appear in a discipline.
Remembering that in our case, there are common disciplines in the different courses, and that is exactly what we want to adjust, the letter position of disciplines with the same name must match.
On this link https://docs.google.com/spreadsheets/d/1_skXPTPzqZtus8KbX9smNYQol7PJK5B1Ad-9HbXDaPk/edit?usp=sharing is the spreadsheet that points out the codes that appear in a given subject, that is, when we look at column K we see that there are letters conflict, and this makes it necessary to adjust for all of them to be the same letter in their respective semesters, which consequently it will optimize the entire operation of the project.
Going a little deeper, I made an inverter script that automatically performs the inversion pointed out from the conflict of the letters, that is, in the face of the example conflict: Production Administration >> C2 | E4 | D5 | A6 | B7 | A9, I define the letter that must be the correct one (without much criteria, in this case the letter A for example) and the script operates the inversion of all the letters in all courses, the inversion keeps the semester, but changes the position. Example, C2 changes to A2.
That's when it starts to get confused, because there was a discipline in A2 and without much discretion I make the inversion of it, putting it now in the place of the one that was in C2. Realize how far this can go is a dead end, humanly impossible to process, and unless I do it through brute force with trial and error, it won't evolve to the best it can be.
I don't know if it's clear for you to understand, I read a little about a similar problem in this topic, Algorithm for creating a school timetable but it talks about time, which is not our case, since what we have in summary here is: reposition subjects so that they are in the best possible lined up in different courses in their slot (letter we define as order within the semester).
I wanted to develop a system that would include a conflict rule in the registration of disciplines for courses (following the criteria mentioned above) and that would also allow processing this adjustment in already created course tables.
I know of several paid and open source academic systems, but I haven't seen any that do something like this with disciplines in course matrices, what exists is the famous timetable that manages the class schedule and it's not exactly a discipline-based repositioning system in conflicts of position in other courses.
I close my first participation here in the hope that someone can read this and help, anyway, it was a pleasure to expose my problem, great day everyone!
