So in order to highlight duplicates on Google Sheets. I use this formula in the Conditional Format

=COUNTIF(A$2:B,$B2)>1

But this doesn't work if I have the same value in the cell, but a different hyperlink.

For example: B1: A (wwww.aaa.com) B2: A (

Although the hyperlink is different, the value is still the same so the formula highlights them both. Is there a way to highlight only the cell with duplicate values and hyperlinks?

Many thanks,

CodePudding user response：

You will need to extract first the url then compare by conditional formatting.

To extract the url, use

=getmyurl()

with this custom function

function getmyurl(){
  var url = SpreadsheetApp.getActiveSheet().getActiveRange().offset(0,-1).getRichTextValue().getLinkUrl()
  return (url)
}

You can hide column B if necessary

CodePudding user response：

To highlight the cell with duplicate values and hyperlinks:

1.) Get the URL from the hyperlink using the following custom formula:

=linkURL(A1)

Replace the A1 with the cell to refer to. Or you can drag down to other cells.

Here's the script for the custom formula, to use this just go to Extensions -> Apps Script -> Paste the codes below

function linkURL(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w \((.*)\)/i);
  try {
    var range = sheet.getRange(args[1]);
  }
  catch(e) {
    throw new Error(args[1]   ' is not a valid range');
  }
  
  var formulas = range.getRichTextValues();
  var output = [];
  for (var i = 0; i < formulas.length; i  ) {
    var row = [];
    for (var j = 0; j < formulas[0].length; j  ) {
      row.push(formulas[i][j].getLinkUrl());
    }
    output.push(row);
  }
  return output
}

2.) Highlight the range to apply the conditional formatting.

3.) For the Custom Formula use the following:

=AND(COUNTIF(A:A,A1)>1,COUNTIF(B:B,B1)>1)

This will check for both the column A (the actual text/value only on the cell) and column B (the link) for duplicates.

Result: