How can I have a kind warning if I am adding a text to the cell which was already added somewhere in previous cells of the same column?
-
Did you had the time to evaluate my solution? – Jacob Jan Tuinstra Apr 28 '13 at 19:19
I prepared the following solution.
Formula
Not case sensitive:
=IF(COUNTA(A2:A)>COUNTA(UNIQUE(A2:A));"Invalid";"Names")
Case sensitive:
=IF(ARRAYFORMULA(COUNTA(LOWER(B2:B)))>ARRAYFORMULA(COUNTA(UNIQUE(LOWER(B2:B))));"Invalid";"Names")
Explained
If you set conditional formatting in A1
on Invalid
to be marked red
, then a visual change will make you realize that a duplicate value has been entered, see pic:
In combination with Google Apps Script, you can really make it more visual. With the following script, that uses the onEdit trigger function, you can make the font color turn red:
Code
function onEdit() {
var sh = SpreadsheetApp.getActive()
var cell = sh.getActiveCell();
var A1Notation = columnToLetter(cell.getColumn()) + '1';
var color = sh.getRange(A1Notation).getValues() == "Invalid" ? 'red' : 'black';
cell.setFontColor(color);
}
// stackoverflow.com/a/21231012; Adam Luske
function columnToLetter(column) {
var temp, letter = '';
while (column > 0) {
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter;
}
Example
I've prepared an example file for you: Avoiding Duplicates.
Note
- you need to fix the headers, in order to keep seeing visual change (without using the script).
- you need to pay attention, because all of this isn't bullet proof !!
-
1
The simplest way may be with Data validation (under Data). Assuming the unnamed column in question is ColumnA, a Custom formula validation rule of:
=countif(A:A,A1)=1
The little red triangles are the warning signs.