How to highlight cell if value duplicate in same c

问题:

I am looking for formula for google spreadsheet highlight cell if value duplicate in same column

can anyone please assist me for this query?

回答1:

Try this:

1. Select the whole column
2. Click Format
3. Click Conditional formatting
4. Click Add new rule
5. Set Format cells if to: Custom formula is:
6. Set value to: =countif(A:A,A1)>1
7. Set the formatting style.
8. Ensure the range applies to your column (e.g., A1:A100).

Done. Anything you write in the A1:A100 cells will be checked, and if there is a duplicate (occurs more than once) then it'll be coloured.

For multiple columns, use countifs.

回答2:

While zolley's answer is perfectly right for the question, here's a more general solution for any range, plus explanation:

    =COUNTIF($A$1:$C$50, INDIRECT(ADDRESS(ROW(), COLUMN(), 4))) > 1

Please note that in this example I will be using the range A1:C50. The first parameter ($A$1:$C$50) should be replaced with the range on which you would like to highlight duplicates!

---

to highlight duplicates:

1. Select the whole range on which the duplicate marking is wanted.
2. On the menu: Format > Conditional formatting...
3. Under Apply to range, select the range to which the rule should be applied.
4. In Format cells if, select Custom formula is on the dropdown.
5. In the textbox insert the given formula, adjusting the range to match step (3).

---

Why does it work?

COUNTIF(range, criterion), will compare every cell in range to the criterion, which is processed similarly to formulas. If no special operators are provided, it will compare every cell in the range with the given cell, and return the number of cells found to be matching the rule (in this case, the comparison). We are using a fixed range (with $ signs) so that we always view the full range.

The second block, INDIRECT(ADDRESS(ROW(), COLUMN(), 4)), will return current cell's content. If this was placed inside the cell, docs will have cried about circular dependency, but in this case, the formula is evaluated as if it was in the cell, without changing it.

ROW() and COLUMN() will return the row number and column number of the given cell respectively. If no parameter is provided, the current cell will be returned (this is 1-based, for example, B3 will return 3 for ROW(), and 2 for COLUMN()).

Then we use: ADDRESS(row, column, [absolute_relative_mode]) to translate the numeric row and column to a cell reference (like B3. Remember, while we are inside the cell's context, we don't know it's address OR content, and we need the content in order to compare with). The third parameter takes care for the formatting, and 4 returns the formatting INDIRECT() likes.

INDIRECT(), will take a cell reference and return its content. In this case, the current cell's content. Then back to the start, COUNTIF() will test every cell in the range against ours, and return the count.

The last step is making our formula return a boolean, by making it a logical expression: COUNTIF(...) > 1. The > 1 is used because we know there's at least one cell identical to ours. That's our cell, which is in the range, and thus will be compared to itself. So to indicate a duplicate, we need to find 2 or more cells matching ours.

---

Sources:

• Docs Editors Help: COUNTIF()
• Docs Editors Help: INDIRECT()
• Docs Editors Help: ADDRESS()
• Docs Editors Help: ROW()
• Docs Editors Help: COLUMN()

回答3:

From the "Text Contains" dropdown menu select "Custom formula is:", and write: "=countif(A:A, A1) > 1" (without the quotes)

I did exactly as zolley proposed, but there should be done small correction: use "Custom formula is" instead of "Text Contains". And then conditional rendering will work.

回答4:

Answer of @zolley is right. Just adding a Gif and steps for the reference.

1. Goto menu Format > Conditional formatting..
2. Find Format cells if..
3. Add =countif(A:A,A1)>1 in field Custom formula is
   • Note: Change the letter A with your own column.

回答5:

Also, you can install add-on Remove Duplicates and find (highlight) or remove duplicate values in columns or sheets.

回答6:

I tried all the options and none worked.

Only google app scripts helped me.

source : https://ctrlq.org/code/19649-find-duplicate-rows-in-google-sheets

At the top of your document

1.- go to tools > script editor

2.- set the name of your script

3.- paste this code :

function findDuplicates() {
  // List the columns you want to check by number (A = 1)
  var CHECK_COLUMNS = [1];

  // Get the active sheet and info about it
  var sourceSheet = SpreadsheetApp.getActiveSheet();
  var numRows = sourceSheet.getLastRow();
  var numCols = sourceSheet.getLastColumn();

  // Create the temporary working sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var newSheet = ss.insertSheet("FindDupes");

  // Copy the desired rows to the FindDupes sheet
  for (var i = 0; i < CHECK_COLUMNS.length; i++) {
    var sourceRange = sourceSheet.getRange(1,CHECK_COLUMNS[i],numRows);
    var nextCol = newSheet.getLastColumn() + 1;
    sourceRange.copyTo(newSheet.getRange(1,nextCol,numRows));
  }

  // Find duplicates in the FindDupes sheet and color them in the main sheet
  var dupes = false;
  var data = newSheet.getDataRange().getValues();
  for (i = 1; i < data.length - 1; i++) {
    for (j = i+1; j < data.length; j++) {
      if  (data[i].join() == data[j].join()) {
        dupes = true;
        sourceSheet.getRange(i+1,1,1,numCols).setBackground("red");
        sourceSheet.getRange(j+1,1,1,numCols).setBackground("red");
      }
    }
  }

  // Remove the FindDupes temporary sheet
  ss.deleteSheet(newSheet);

  // Alert the user with the results
  if (dupes) {
    Browser.msgBox("Possible duplicate(s) found and colored red.");
  } else {
    Browser.msgBox("No duplicates found.");
  }
};

4.- save and run

In less than 3 seconds, my duplicate row was colored. Just copy-past the script.

If you don't know about google apps scripts , this links could be help you:

https://zapier.com/learn/google-sheets/google-apps-script-tutorial/

https://developers.google.com/apps-script/overview

I hope this helps.