I am trying to check word lists across different rows to check how many words students know from previous lessons. I have a table like the one shown here: https://docs.google.com/spreadsheets/d/17mf7loqm9eaI_t5XNl0OShDBgCPi8cvidRaLedcBnFo/edit?gid=0#gid=0
I would like to do the following:
I will need to be able to have a solution that adapts as and when I change things. Sometimes I may change the words or the word order across the different rows as the lessons progress or the requirements change. There is no limit for the number of columns that may be used in each row.
EDIT: other answers have now posted much simpler methods using Google Sheets formulae, but I will leave this one here for completeness (some stuff can be custom-tweaked more easily, such as accents/case sensitivity, the ranges, etc). Moreover, after testing doubleunary's conditional formatting formula, the Apps Script solution appears to be faster (much to my surprise).
My proposal is to use Apps Scripts.
For that, please click on the 'Extensions' menu, then 'Apps Script'. Erase all the "myFunction" stuff. Now for your two questions.
/**
* Checks if a cell contains either one of two words,
* in which case highlights or unhighlights the cell respectively.
* Returns whether the cell has been unhighlighted.
*
* @param {Range} cell - The cell.
* @param {String} referenceWordHighlight - The first word \
* to be compared to; the cell will be highlighted.
* @param {String} [referenceWordWhite] - The second word \
* to be compared to; the cell's background will be set white.
* @return {Boolean} Whether the cell has been unhighlighted.
*/
function updateCell(cell, referenceWordHighlight, referenceWordWhite) {
// The number of the first row with words.
const startRow = 2;
// The number of the first column with words.
const startCol = 4;
const word = cell.getValue();
if (cell.getRow() >= startRow && cell.getColumn() >= startCol) {
if (word.localeCompare(referenceWordHighlight, 'es', {sensitivity: 'accent'}) === 0) {
// Set the highlight colour of the cell.
cell.setBackground('peachpuff');
return false;
}
else if (referenceWordWhite != undefined &&
word.localeCompare(referenceWordWhite, 'es', {sensitivity: 'accent'}) === 0) {
cell.setBackground('white');
return true;
}
}
return false;
}
/**
* Checks if the edited cell's word has already appeared before, \
* in which case colours said cell. Updates any following cells \
* that contain the edited cell's new word.
*
* @param {Event} e - The editing event.
* @return {void} Nothing.
*/
function onEdit(e) {
// The number of the first row with words.
const startRow = 2;
// The number of the first column with words.
const startCol = 4;
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
const editedCell = e.range;
const editedRow = editedCell.getRow();
const newWord = editedCell.getValue();
const oldWord = e.oldValue;
const textFinder = ss.createTextFinder(newWord)
.matchCase(false)
.matchEntireCell(true)
.startFrom(sheet.getRange(startRow, startCol-1));
const cellFirstOccurrence = textFinder.findNext();
const doesCellBecomeHighlighted = (cellFirstOccurrence.getRow() < editedRow &&
cellFirstOccurrence.getRow() >= startRow &&
cellFirstOccurrence.getColumn() >= startCol) ||
(cellFirstOccurrence.getRow() === editedRow &&
cellFirstOccurrence.getColumn() < editedCell.getColumn());
if (doesCellBecomeHighlighted) {
// Set the highlight colour of the cell.
editedCell.setBackground('peachpuff');
}
else {
editedCell.setBackground('white');
let someCellHasBeenUnhighlighted = false;
for (var j=editedCell.getColumn()+1; j<=getLastColumnInRow(editedRow); j++) {
const cell = sheet.getRange(editedRow, j);
if (!someCellHasBeenUnhighlighted) {
someCellHasBeenUnhighlighted = updateCell(cell, newWord, oldWord);
}
else {
updateCell(cell, newWord);
}
}
for (var i=editedRow+1; i<=sheet.getLastRow(); i++) {
for (var j=startCol; j<=getLastColumnInRow(i); j++) {
const cell = sheet.getRange(i, j);
if (!someCellHasBeenUnhighlighted) {
someCellHasBeenUnhighlighted = updateCell(cell, newWord, oldWord);
}
else {
updateCell(cell, newWord);
}
}
}
}
}
In that code, you can modify two sets of things:
startRow and startCol numbers.peachpuff with any other colour name that you can find here: named colours. If you do that, please remember to leave the apostrophes: write setBackground('red'), not setBackground(red).EDIT: in my previous version, I had made two constraining assumptions. Per the OP's request, I have updated the code to work in a more general scenario - the price to pay being its increased complexity.
/**
* Gets the column of the last non-empty cell in a given row.
*
* @param {number} rowNumber - The number of the row.
* @return {number} The number of the column.
*/
function getLastColumnInRow(rowNumber) {
// Get the sheet.
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Get the last used column in the sheet.
const maxLastCol = sheet.getLastColumn();
// Get the whole row.
const row = sheet.getRange(rowNumber, 1, 1, maxLastCol);
// Get the values in the row.
const rowValues = row.getValues();
// Initialise the column number with the largest possible value.
let n = maxLastCol;
// Check if the last cell in the row is empty.
let isLastCellBlank = rowValues[0][n] == '';
// While the cell is empty,
while (isLastCellBlank) {
// decrease the column number,
n--;
// and check the new column's cell.
isLastCellBlank = rowValues[0][n] == '';
}
// Return the column number.
return n;
}
/**
* Counts the number of cells with a white background in a given range.
*
* @param {number} startRow - The range's top left cell's row number.
* @param {number} startCol - The range's top left cell's column number.
* @param {number} [numRows=1] - The number of rows in the range.
* @param {number} [numCols] - The number of columns in the range.\
* By default the number of columns until the last non-blank column in the sheet.
* @return {number} The total number of white-coloured cells.
* @customfunction
*/
function COUNTWHITEBG(startRow, startCol, numRows=1, numCols) {
// Get the sheet.
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// If the parameter numCols hasn't been passed,
if (numCols === undefined) {
// if further the range consists of only one row,
if (numRows === 1) {
// get the column number of the last non-empty cell in the row.
numCols = getLastColumnInRow(startRow);
}
else {
// Otherwise, get the last non-empty column in the sheet.
numCols = sheet.getLastColumn();
}
}
// Get the range.
const range = sheet.getRange(startRow, startCol, numRows, numCols);
// Get the list of values.
const values = range.getValues().flat();
// Get the list of background colours.
const colours = range.getBackgrounds().flat();
// Count the number of non-empty white-coloured cells.
// For each cell in the range,
const numNonEmptyWhiteCells = values.reduce(function (accumulator, value, index) {
// check if it is blank,
const isBlank = value == '';
// and check if it is white.
const isWhite = colours[index] == '#ffffff';
// If it is white and non-empty, count it; otherwise, don't.
const thisCell = !isBlank && isWhite ? 1 : 0;
// Add it to the accumulated sum.
return accumulator + thisCell;
}, 0,);
// Return the final number.
return numNonEmptyWhiteCells;
}
You can leave that code as it is. You now just need to enter the appropriate formula in your C column. For example, in cell C2 you should write: =COUNTWHITEBG(ROW(D2), COLUMN(D2)), where D2 is the first cell with a word. Then you can just drag the formula down the column as per usual in Google Sheets.
There you go! I haven't chosen the simplest path, but that depends on the user. Have a nice one.