Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question
    
Did you had the time to evaluate my solution? –  Jacob Jan Tuinstra Apr 28 '13 at 19:19

1 Answer 1

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

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 !!
share|improve this answer
1  
Nice solution. I really enjoyed it. –  Saeed Neamati Sep 21 '13 at 14:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.