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've prepared an example file for you: Avoiding Duplicates. The only formula used, is the following:

=IF(COUNTA(A2:A)>COUNTA(UNIQUE(A2:A));"Invalid";"Names")

UPDATE
Added example for None Case Sensitive entries:
=IF(ARRAYFORMULA(COUNTA(LOWER(B2:B)))>ARRAYFORMULA(COUNTA(UNIQUE(LOWER(B2:B))));"Invalid";"Names")

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:

function onEdit() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var status = sh.getRange("C1").getValues();
  var cell = sh.getActiveCell();

  if(status == "Invalid") {
    cell.setFontColor('red');
  } else {
    cell.setFontColor('black');
  } 
}

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

Prerequisites:

  1. you need to fix the headers, in order to keep seeing visual change.
  2. 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.