How to iterate cells in a Google sheet formula?-CodePudding

I'm creating a solution to automatically detect conflicts in a schedule created with Google sheets. A "conflict" means that in the same day at the same time different lessons are supposed to have same teacher or to be in the same room. For instance, in the table below groups 1 and 2 are supposed to be in room 2 at the same time, which should be indicated as "conflict".

                           Monday                              Tuesday                ...
              room   subject   teacher   tutor     room   subject   teacher   tutor   ...
(group1)
09:00-10:00    1     math      Smith     Black
10:00-10:45    2     science   Stones    Moore
...
(group2)
10:00-10:45    2     math      Smith     Taylor
10:55-11:40    1     reading   Anderson  Harris
...

To check if there are any "same teacher" conflicts, I've added the following formula:

=if(OR(ARRAYFORMULA(D7={D19;D29}));"at this time teacher has another class for _ group";"ok")

However, such solution has some drawbacks.

The main one is: the {D19;D29;...} array is formed manually which is not nice, at least very fragile. If a line is added in the middle of the schedule, most of the checks will break. What I want to do instead is to get the necessary lines by filtering those with the same time in column A and then get cells D to compare.

Another one is, I can't get (and show) the "conflicting" group which this teacher also is supposed to teach (unless I manually create another array, corresponding to {D19;D29;...} which is more manual work), see _ in the formula.

My question is, can I create some form of loop/iterating in Google sheets to deal with this issues in some convenient coding manner? Or may be you'd suggest another approach to this?

CodePudding user response：

There is a tab on the sample sheet called Conflict Finder (Simpler) where you will find this formula in cell G3, and a very similar one in H3. It counts instances of a common timeslot and teacher name and if it's more than 1, it outputs the word "Conflict"

=ARRAYFORMULA({"Teacher Conflicts";IF(COUNTIFS($A4:$A;$A4:$A;D4:D;D4:D)>1;"Conflict";)})

Once columns with the array formula are created, you can apply conditional formatting with custom formula to highlight the conflicting cells and hide the auxiliary columns. For instance, for range D4:D1000 apply =LEN(G4) to give red background (in fact, range D4:E1000 also works since column H contains conflicts for column E).