Google-sheets – Placing text from one cell between certain characters of another sentence

You can do this by applying to range A1:A the conditional formatting with custom formula

=counta(unique(transpose(split(A1&", "&JOIN(", ",B$1:B),", "))))>counta(unique(B$1:B))

Explanation.

• To the right of the inequality sign, counta(unique(B$1:B)) counts the number of distinct words in column B.
• On the left, A1&", "&JOIN(", ",B$1:B) concatenates A1 with the content of column B, comma-and-space separated.
• Then split(...,", ") separates all of this into a row of words
• Then counta(unique(transpose(...))) counts the number of distinct words among these.
• Formatting is applied if the second count is greater than the first.

Since the content of L7 is going to be overwritten, spreadsheet formulas cannot perform such reset. You need a script that has the spreadsheet keys of all the spreadsheets to be changed, and has the authority to edit them (i.e., the Google account under which it runs has this authority). It can be either a stand-alone script, or bound to your master spreadsheet, whichever is more convenient. Also, you'll need to know the name of the sheet within the spreadsheet in which the cell L7 is located; apparently it's "FailingStudents".

function reset() {
  var keys = ['....', '....' ]; // list of spreadsheet ids
  for (var i = 0; i < keys.length; i++) {
    var ss = SpreadsheetApp.openById(keys[i]);
    var sheet = ss.getSheetByName('FailingStudents');
    sheet.getRange('L7').setValue('No');
  }
}

Once you run this, the cell FailingStudents!L7 will be set to No in all listed spreadsheets.