1

In a Google spreadsheet doc, I need to set the cell colour based in duplicated value in column with different colours for each duplicated values. The colour should be set to 'random' so that all of them will be different.

After the condition is applied the colour of the cells should look this: https://docs.google.com/spreadsheets/d/1YuUjg_PqD53AoTrxgvnEHYwZ_disqvNKDMmp5dRYq4I/edit?usp=sharing

This question is similar to this How to highlight cell if value duplicate in same column for google spreadsheet? but not the same since I need to give different colours for each duplicated value

I guess I might need a script to get this done? I don't think this could be done with 'conditional formatting' rule feature.

UPDATE: The conditionals posted by Player0 works great. But the problem is that in the example I just posted a few cells. In the real spreadsheet I have hundreds of values which change from time to time. Then with this approach I will need to create a lot of condicional rules. I need to do this dynamically with a single formula. It should be a single formula/script to be applied to Column A which generate a random color for each value coincidence...

JPashs
  • 13,044
  • 10
  • 42
  • 65

3 Answers3

1

yellow:

=(ARRAYFORMULA(VLOOKUP(A1; {UNIQUE(A$1:A)\ 
  ROW(INDIRECT("A1:A"&COUNTUNIQUE(A$1:A)+1))}; 2; 0))=1)*
 (COUNTIF(A:A; A1)>1)

blue:

=(ARRAYFORMULA(VLOOKUP(A1; {UNIQUE(A$1:A)\ 
  ROW(INDIRECT("A1:A"&COUNTUNIQUE(A$1:A)+1))}; 2; 0))=2)*
 (COUNTIF(A:A; A1)>1)

pink:

=(ARRAYFORMULA(VLOOKUP(A1; {UNIQUE(A$1:A)\ 
  ROW(INDIRECT("A1:A"&COUNTUNIQUE(A$1:A)+1))}; 2; 0))=3)*
 (COUNTIF(A:A; A1)>1)

green:

=(ARRAYFORMULA(VLOOKUP(A1; {UNIQUE(A$1:A)\ 
  ROW(INDIRECT("A1:A"&COUNTUNIQUE(A$1:A)+1))}; 2; 0))=4)*
 (COUNTIF(A:A; A1)>1)

red:

=(ARRAYFORMULA(VLOOKUP(A1; {UNIQUE(A$1:A)\ 
  ROW(INDIRECT("A1:A"&COUNTUNIQUE(A$1:A)+1))}; 2; 0))=5)*
 (COUNTIF(A:A; A1)>1)

orange:

=(ARRAYFORMULA(VLOOKUP(A1; {UNIQUE(A$1:A)\ 
  ROW(INDIRECT("A1:A"&COUNTUNIQUE(A$1:A)+1))}; 2; 0))=6)*
 (COUNTIF(A:A; A1)>1)

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • Sorry for my delay. The conditional works great, thanks. But the problem is that in the example I just posted a few cells. In the real spreadsheet I have hundreds of values which change from time to time. Then with your approach I will need to create a lot of condicional rules. Is there a way to do this dynamically with a single formula? Maybe a single formula which generate a random color for each value coincidence... – JPashs Jan 24 '20 at 09:58
  • You will be able to edit this sheet just in case you need it: https://docs.google.com/spreadsheets/d/1YuUjg_PqD53AoTrxgvnEHYwZ_disqvNKDMmp5dRYq4I/edit?usp=sharing – JPashs Jan 24 '20 at 10:01
  • this is the only way. but you could try some scripted solutions which may work – player0 Jan 24 '20 at 11:39
  • Ok, thanks. I'm afraid I don't have the knowledge to create this script. I just started a bounty, just in case in you are interested in this. – JPashs Jan 24 '20 at 14:36
  • The formula can be shortened as `=A1=INDEX(UNIQUE($A$1:$A);2;1)` … `=A1=INDEX(UNIQUE($A$1:$A);999;1)`. You start at `2` to skip the blank value. – etuardu Nov 10 '22 at 15:55
0

Here is a script that works:

function colorDuplicates() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var column = 1
  ss.getRange(2, column, lr).setBackground(null);
  var color = ["#EA9999","#F9CB9C","#FFE599","#B6D7A8","#A2C4C9","#9FC5E8","#B4A7D6","#D5A6BD","#CCCCCC","#B45F06","#666666","#FF0000","#FF9900","#FFFF00","#00FF00","#00FFFF"];
  var c = 0;
  var checkcolor = false;
  for (var i = 2; i < lr+1;i++){
    if (checkcolor == true) {
      c++;
      checkcolor = false;
    }
    var a = ss.getRange(i, column).getValue();
    if (a == "") {continue;}
    var cellcolor = ss.getRange(i, column).getBackground();
    if (cellcolor != "#ffffff") {continue;}
    for (var j = i+1;j< lr+1;j++){
      var b = ss.getRange(j, column).getValue();
      if (a != b) {continue;}
      var cellcolor = ss.getRange(j, column).getBackground();
      if (cellcolor != "#ffffff") {continue;}
      ss.getRange(i, column).setBackground(color[c]);
      ss.getRange(j, column).setBackground(color[c]);
      checkcolor = true;
    }
  }
}



function colorDuplicates2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = getLastRowSpecial(ss.getRange("A:A").getValues());
  var lc = ss.getLastColumn();
  ss.getRange(2, 1, lr).setBackground(null);
  ss.insertColumnAfter(lc);
  ss.getRange(1, lc+1).setFormula("=FILTER(UNIQUE(A2:A);UNIQUE(A2:A)<>\"\")"); // Change the ";" for a "," if you are in the us
  var numValues = getLastRowSpecial(ss.getRange(1, lc+1, lr).getValues());
  var values = new Array(36);
  for (var a = 1;a<numValues+1;a++){
    values[a] = String(ss.getRange(a, lc+1).getValue());
  }

  var color = ["#980000", "#ff0000", "#ff9900", "#ffff00", "#00ff00", "#00ffff", "#4a86e8", "#0000ff", "#9900ff", "#ff00ff", "#e6b8af", "#f4cccc", "#fce5cd", "#ea9999", "#f9cb9c", "#ffe599", "#b6d7a8", "#a2c4c9", "#a4c2f4", "#9fc5e8", "#b4a7d6", "#d5a6bd", "#cc4125", "#e06666", "#f6b26b", "#ffd966", "#93c47d", "#76a5af", "#6d9eeb", "#6fa8dc", "#8e7cc3", "#c27ba0", "#a61c00", "#cc0000", "#e69138", "#f1c232", "#6aa84f", "#45818e", "#3c78d8", "#3d85c6", "#674ea7", "#a64d79", "#85200c", "#990000"];

  for (var i = 2;i<lr+1;i++){
    switch (String(ss.getRange(i, 1).getValue())) {
      case values[1]:
        ss.getRange(i, 1).setBackground(color[1]);
        break;
      case values[2]:
        ss.getRange(i, 1).setBackground(color[2]);
        break;
      case values[3]:
        ss.getRange(i, 1).setBackground(color[3]);
        break;
      case values[4]:
        ss.getRange(i, 1).setBackground(color[4]);
        break;
      case values[5]:
        ss.getRange(i, 1).setBackground(color[5]);
        break;
      case values[6]:
        ss.getRange(i, 1).setBackground(color[6]);
        break;
      case values[7]:
        ss.getRange(i, 1).setBackground(color[7]);
        break;
      case values[8]:
        ss.getRange(i, 1).setBackground(color[8]);
        break;
      case values[9]:
        ss.getRange(i, 1).setBackground(color[9]);
        break;
      case values[10]:
        ss.getRange(i, 1).setBackground(color[10]);
        break;
      case values[11]:
        ss.getRange(i, 1).setBackground(color[11]);
        break;
      case values[12]:
        ss.getRange(i, 1).setBackground(color[12]);
        break;
      case values[13]:
        ss.getRange(i, 1).setBackground(color[13]);
        break;
      case values[14]:
        ss.getRange(i, 1).setBackground(color[14]);
        break;
      case values[15]:
        ss.getRange(i, 1).setBackground(color[15]);
        break;
      case values[16]:
        ss.getRange(i, 1).setBackground(color[16]);
        break;
      case values[17]:
        ss.getRange(i, 1).setBackground(color[17]);
        break;
      case values[18]:
        ss.getRange(i, 1).setBackground(color[18]);
        break;
      case values[19]:
        ss.getRange(i, 1).setBackground(color[19]);
        break;
      case values[20]:
        ss.getRange(i, 1).setBackground(color[20]);
        break;
      case values[21]:
        ss.getRange(i, 1).setBackground(color[21]);
        break;
      case values[22]:
        ss.getRange(i, 1).setBackground(color[22]);
        break;
      case values[23]:
        ss.getRange(i, 1).setBackground(color[23]);
        break;
      case values[24]:
        ss.getRange(i, 1).setBackground(color[24]);
        break;
      case values[25]:
        ss.getRange(i, 1).setBackground(color[25]);
        break;
      case values[26]:
        ss.getRange(i, 1).setBackground(color[26]);
        break;
      case values[27]:
        ss.getRange(i, 1).setBackground(color[27]);
        break;
      case values[28]:
        ss.getRange(i, 1).setBackground(color[28]);
        break;
      case values[29]:
        ss.getRange(i, 1).setBackground(color[29]);
        break;
      case values[30]:
        ss.getRange(i, 1).setBackground(color[30]);
        break;
      case values[31]:
        ss.getRange(i, 1).setBackground(color[31]);
        break;
      case values[32]:
        ss.getRange(i, 1).setBackground(color[32]);
        break;
      case values[33]:
        ss.getRange(i, 1).setBackground(color[33]);
        break;
      case values[34]:
        ss.getRange(i, 1).setBackground(color[34]);
        break;
      case values[35]:
        ss.getRange(i, 1).setBackground(color[35]);
        break;
      case values[36]:
        ss.getRange(i, 1).setBackground(color[36]);
        break;

    }
  }
  ss.deleteColumn(lc+1);
}



function getLastRowSpecial(range){
  var rowNum = 0;
  var blank = false;
  for(var row = 0; row < range.length; row++){

    if(range[row][0] === "" && !blank){
      rowNum = row;
      blank = true;
    }else if(range[row][0] !== ""){
      blank = false;
    };
  };
  return rowNum;
}

It has only 16 colors, and it will leave blank the duplicates that finds after that, but you can add as many more colors as you need.

It works for finding duplicates in column A, but you can also change that if you need it to be another column.

Try it and feel free to ask me anything. Good luck!

*I edited my previous answer beacuse it didnt take in to account values that appeard more than twice.

*I edited again to avoid empty cells.

user11221377
  • 125
  • 9
  • Hello, it only add cell color to the first pair of matches.Capture here: https://nimb.ws/1Bma2r BTW: I saved the script as a Macro and then I run the script. – JPashs Jan 28 '20 at 08:32
  • I think the problem is that the script also check the empty cells. Is there a way to avoid to check the cell with no values? – JPashs Jan 28 '20 at 08:50
  • Yes, sorry about that. I just fixed the empty cells problem. You can try it again. – user11221377 Jan 28 '20 at 12:34
  • Thanks, it works, but it takes a lot (a lot...) of time to scan the data, and just have 12 rows. The sheet has 1000 rows. Is there a way to optimize the script? – JPashs Jan 28 '20 at 15:47
  • I think the script still checks the blank cells. I deleted all data and run the script and it took one minute until it stops. Are you sure the script avoids to check the cells with no values? – JPashs Jan 28 '20 at 16:41
  • I think that this line 'var lr = ss.getLastRow();' should get the last row which has a value and not the last row of the sheet. Otherwise the for() function will run as many times as rows in the sheet. – JPashs Jan 28 '20 at 16:50
  • How do I change the column? – JPashs Jan 29 '20 at 12:30
  • I just added a column variable, just change the "1" for the number of column you want. I also added another script that may help you. I'm sorry I don't know how to make it faster than that. – user11221377 Jan 29 '20 at 14:43
0

You can use a script function to take advantage of a hashing algorithm like md5 and obtain an (almost) unique byte string for any cell value.

Selecting the first 3 bytes and adding a leading "#", you can convert such a hash into a hex color.

After that, looping over the range values to determine the color codes and setting the background is all that is required.

/**
 * Return an md5 hash as hex string.
 * @example md5("lorem ipsum") => "80a751fde577028640c419000e33eba6"
 */
function md5(txt) {
  return Utilities.computeDigest(
    Utilities.DigestAlgorithm.MD5,
    txt
  ).map(
    c => (255 & c).toString(16).padStart(2, '0')
  ).join(
    ""
  );
}

/**
 * Return a hex color from any string.
 * @example txt2color("lorem ipsum") => "#80a751"
 */
function txt2color(txt) {
  return "#" + md5(txt).slice(0, 6);
}

/**
 * Set the background color of the cells in the active
 * range so that the cells with same value get the
 * same background color
 */
function highlight_same_values() {
  const range = SpreadsheetApp.getActiveRange();
  const bgcolors = range.getValues().map(
    row => row.map(
      cell => cell ? txt2color(cell) : null
    )
  );
  range.setBackgrounds(bgcolors);
}
etuardu
  • 5,066
  • 3
  • 46
  • 58