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...
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.