Avoiding duplicates when adding cells in column one by one in Google Spreadsheets

Question

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?

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:

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