Highlighting non-first instance duplicates of words and counting highlighted cells

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:

  1. Highlight every instance (except the first) where a word is repeated in any row after the first instance.
  2. Count how many of the cells in each row ARE NOT highlighted (meaning they are new words)

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.

6

  1. Highlight every instance (except the first) where a word is repeated in any row after the first instance.

Use this conditional formatting custom formula rule for the range D2:Z:

=countif($D$1:1, D2) + countif($C$2:C2, D2)

The rule will work with any number of rows and columns.

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.

  1. Highlighting repeated words. Here is some code that should do what you want if you copy-paste it:
/**
 * 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:

  • the row and column numbers of the first word. I’ve used row 2 and column D (=4), as in your example; but if you ever need to change that, you just need to update the startRow and startCol numbers.
  • the colour used for “highlighting” the cells. I’ve tried to find a similar colour to the one you used; but, once again, you can change that. You just need to replace the 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.

  1. Count how many cells are not highlighted. Once again, copy-paste the following code:
/**
 * 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.

New contributor

user27425627 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

5

Here’s a generalized approach (scenario 2) which you may adapt accordingly:

=byrow(D2:F,lambda(Σ,if(counta(Σ)=0,,sumproduct(--(countif(D2:index(F:F,row(Σ)),Σ)=1)))))

Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa Dịch vụ tổ chức sự kiện 5 sao Thông tin về chúng tôi Dịch vụ sinh nhật bé trai Dịch vụ sinh nhật bé gái Sự kiện trọn gói Các tiết mục giải trí Dịch vụ bổ trợ Tiệc cưới sang trọng Dịch vụ khai trương Tư vấn tổ chức sự kiện Hình ảnh sự kiện Cập nhật tin tức Liên hệ ngay Thuê chú hề chuyên nghiệp Tiệc tất niên cho công ty Trang trí tiệc cuối năm Tiệc tất niên độc đáo Sinh nhật bé Hải Đăng Sinh nhật đáng yêu bé Khánh Vân Sinh nhật sang trọng Bích Ngân Tiệc sinh nhật bé Thanh Trang Dịch vụ ông già Noel Xiếc thú vui nhộn Biểu diễn xiếc quay đĩa Dịch vụ tổ chức tiệc uy tín Khám phá dịch vụ của chúng tôi Tiệc sinh nhật cho bé trai Trang trí tiệc cho bé gái Gói sự kiện chuyên nghiệp Chương trình giải trí hấp dẫn Dịch vụ hỗ trợ sự kiện Trang trí tiệc cưới đẹp Khởi đầu thành công với khai trương Chuyên gia tư vấn sự kiện Xem ảnh các sự kiện đẹp Tin mới về sự kiện Kết nối với đội ngũ chuyên gia Chú hề vui nhộn cho tiệc sinh nhật Ý tưởng tiệc cuối năm Tất niên độc đáo Trang trí tiệc hiện đại Tổ chức sinh nhật cho Hải Đăng Sinh nhật độc quyền Khánh Vân Phong cách tiệc Bích Ngân Trang trí tiệc bé Thanh Trang Thuê dịch vụ ông già Noel chuyên nghiệp Xem xiếc khỉ đặc sắc Xiếc quay đĩa thú vị
Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa
Thiết kế website Thiết kế website Thiết kế website Cách kháng tài khoản quảng cáo Mua bán Fanpage Facebook Dịch vụ SEO Tổ chức sinh nhật