CODEWITHSUNDEEP
google-sheets
Iris Ho
Iris Ho

Reputation: 35

Using data validation to reject duplicate entries and blank cells in a column

I would really appreciate it if anyone could write a data validation function that prevents someone from entering duplicate entries AND blank columns.

For example, if I entered

111
999
111

I want google sheets to reject 111.

Also, if I entered

111
999

123

I want google sheets to reject 123.

Upvotes: 1

Views: 2705

Answers (1)

user3717023
user3717023

Reputation:

Assuming that the column is A and the cell A1 is its header, the validation rule to apply to A2:A would be "Custom formula is..." with the formula

=and(iserror(match(A2, A$1:A1, 0)), not(isblank(A1)))

Explanation:

  • match attempts to find each cell's value in the cells above it. If there is no match, it returns #N/A error, which iserror converts to TRUE value.
  • The second condition insists that the cell directly above the current one is not blank.
  • The two are joined by and, so they must both hold in order for input to be valid.

Upvotes: 1

Related Questions