Highlight Duplicates with Similar Hyperlink

0
votes

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 (www.bbb.com)

enter image description here

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,

2
google-apps-scriptgoogle-sheetsgoogle-sheets-formulaspreadsheet
Are the hyperlinks entered using the hyperlink() function? (As always, please share example sheet.) - Argyll
Hi @Argyll. I created this example sheet. Does this work? Thanks - Rex Nguyen

2 Answers

0
votes

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

enter image description here

0
votes

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. enter image description here

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:

enter image description here