68

I have created a Google spreadsheet (acting as a database) that has numerous columns, name being one of them, and I wanted to make sure the name field is always unique and no row can be created if the name field is the same as another row.

Essentially I want to create a primary key for a database. Does anyone know how to do this in Google Spreadsheets?

If it helps, I created a form to go with the Google Spreadsheet (database) that will enter the data in to the sheet and would love to ensure a user does not enter the same name as someone else in the list already.

3
  • 7
    OK, this needs to be said: Just like Excel, Google Spreadsheets aren't a database engine. You might want to consider some kind of real database backend that could do this correctly. We have 20+ years of very bad experience with people using Excel as a database, and I'd hate to see everyone have to learn those lessons all over YET AGAIN. Spreadsheet != database. Learn it, live it, love it. Commented Dec 7, 2011 at 0:41
  • I answered a similar question: Google Spreadsheets: Multiple cells with same drop-down list, but not duplicate selections
    – user79865
    Commented Jul 7, 2015 at 22:46
  • 4
    Excel, text files, and lines in sand can be a database, if used properly. That said, the massive-multiplayer feature of Google Sheets means it would be an awesome candidate to add a few more database features in it. Commented Nov 24, 2015 at 16:48

5 Answers 5

100
=COUNTIF($A:$A,"="&A1)  < 2

If you put this in as a Custom Formula for the Data Validation rule for Column A, Column A will reject all duplicates.

6
  • 1
    This is a brilliantly simple way to highlight duplicates without getting into any scripting. You just saved my morning.. thanks!
    – Matt
    Commented Aug 10, 2016 at 10:20
  • 5
    Could you please explain what the formula means? What does A1 stand for? Should I change it to A2 if my data start at the second row? Commented May 17, 2018 at 18:53
  • 3
    @TomášZato-ReinstateMonica: References in a data validation formula are applied in the same way of a fill-down operation. Example: when this validation is applied to the column A, then A1 gets =COUNTIF(A:A,"="&A1) < 2, A2 gets =COUNTIF(A:A,"="&A2) < 2 and so on. ` . If your column is E, then you'd write =COUNTIF(E:E, "="&E1) < 2. If your data starts at the second row, then =COUNTIF(E$2:E, "="&E1) < 2.
    – tokland
    Commented Jan 7, 2020 at 21:01
  • @tokland When I use this formula all cells are marked as violating the validation. Any ideas why? I just want to prevent that duplicates are entered in a specific column (C) (which has one header row).
    – d-b
    Commented Jun 22, 2021 at 10:37
  • This is neat. Unfortunately copying and pasting a value from the same column defeats "Reject Input". The little red warning triangle still pops us, but I found that it takes a long time for a spreadsheet with >1000 rows (like 10-20sec). Commented Feb 22, 2022 at 17:55
10

I do not have a solution if you insist on using a form, but otherwise I have a very simple solution: Let's say that the unique column is A. Then you create the following data validation rule on A2 (the 1st record after the header): =COUNTIF($A$1:$A$999,A2)<=1 . Then, you copy the cell and select the entire column, right-click, expand the paste special submenu, and click on Paste data validation only. That's it!

1
  • This is great! Instead of "data validation" I wanted "conditional formatting", with all duplicates highlighted red. In that case, you just have to invert the condition to =COUNTIF($A$1:$A$999,A2)>1 so it evals to true whenever it's a dupe. Then, "Paste conditional formatting".
    – Nick Crews
    Commented Aug 9, 2023 at 20:31
2

You are asking for the opposite of data validation from a list. In effect you want the data validation to fail, instead of succeed, if the value is in the list. This isn't possible with data validation, but a script can do it.

Consider the following script. This script monitors all edits, and pops a message box when a cell value duplicates any other cell value in the same column.

function onEdit( event )
{
  // Store the edited sheet.
  var sheet_active = event .source .getActiveSheet() ;

  // Store the edited range.
  var range_active = event .source .getActiveRange() ;

  // Store the row, column, and value of the edited cell.
  var row_edited = range_active .getRow() ;
  var column_edited = range_active .getColumn() ;
  var value_edited = range_active .getValue() ;

  // Store a range consisting of the column containing the edited cell.
  var range_column_edited = sheet_active .getRange(
    1 , column_edited ,
    sheet_active .getMaxRows() , 1
  ) ;

  // Store an array consisting of the values in the column.
  var values_column_edited = range_column_edited .getValues() ;

  // Compare each value to the edited cell.
  for( var r = 0 ; r < values_column_edited .length ; r++ )
  {
    if( r+1 == row_edited ) continue ;
    if( values_column_edited[r] == value_edited )
      Browser .msgBox(
        'value_edited="'
        + value_edited
        + '" values_column_edited['
        + r
        + ']="'
        + values_column_edited[r]
        + '"'
      ) ;
  }
}

There will be various practical refinements needed. For example, you might elect to monitor only certain columns, and you might elect to take additional action, such as blanking out the cell value. You may need special handling for blank (missing) values. But this gives you the basic technique that will let you validate.

Update:

To elaborate on the original answer I thought I'd add a few of the validations I personally use that were mentioned in the answer.

// Here's a function I use to assure that only one cell is being edited.

function isRangeSingleCell(range) {
  if(range.getRow() === range.getLastRow() && range.getColumn() === range.getLastColumn()) { return true; }
}

To use it just skip the validation if more than one cell is edited

if(!isRangeSingleCell(range_active)) { return; }

You can also skip the validation if the row is not the first row:

if(range_active.getRowIndex() != 1) { return; }

Note: I can't remember off the top of my head if the row counting starts at 0 or 1 so this code may have a bug

The key to onEdit validations is to exit as early as possible to save from unnecessary computation. The quickest exit from a function is an empty return statement.

2

I managed getting this done by using this validation formula (example given for column B with the first line being the title):

  1. Select all but the first cell of the column (click B then Ctrl+click B2)
  2. Enable data validation (at the end of the right click menu)
  3. Enter YourTab!B2:B for the cell range
  4. Use 'Custom formula is' then use =COUNTUNIQUE(B2:B)=COUNT(B2:B).

That way, every duplicate entry will instantly mark all entries before the wrong one.

-1

Here is how I do this:

  1. Select all of column B (click B).
  2. In Data Validation, enter the formula =COUNTIF($B2:$B,"="&indirect(address(row(),column()))) < 2
  3. Adjust the Cell Range as needed, maybe at least to exclude the header. Make sure to include some headroom at the bottom to allow for new rows to be created.

Formula breakdown, starting from the inside: row() and column() retrieve the row and column number of the current cell, i.e., the cell being evaluated at any given time.

address(...) creates a cell reference with a row number and column number.

indirect(...) evaluates a cell reference.

countif(...) counts the cells in a range that satisfy a condition. In this instance, it returns the number of cells that are equal to the current cell.

Finally, we ensure that that number returned by countif is smaller than 2.

For a cell to be valid, there must be no more than 1 cell in the range whose value is equal to the current cell.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.