Unique Value Validation Rule

[Erik]

Hi guys, 

I'm trying to set a validation rule on my "email" column so that all rows inserted by user contain unique emails. 

Normally i would do something like this with a counif formula. 

My problem is that i want this to be dynamic and apply to each new row.     

validation: {
from:"AND(COUNTIF(A:A,A3)=1)",
dataType: "custom",
messageTemplate: "Email must be unique",
type: "reject"
}

[Stefan]

Hello Erik,

This can be achieved with the COUNTIF formula. Please use absolute references for the column that needs to be validated. Apply this validation rule for the whole column - "AND(COUNTIF($A:$A,A3)=1)".

More information can be found at:

https://www.ablebits.com/office-addins-blog/2013/10/20/prevent-duplicates-in-excel-column/

I hope this is helpful.

Regards,
Stefan
Telerik by Progress

 

 

[Erik]

Thanks Stefan, indeed, this worked for me. 

The code was: 

var emailRange = spreadShit.activeSheet().range("A1:A100");
emailRange.validation({
                dataType: "custom",
                from: "AND(COUNTIF($A:$A,$A1)=1)",
                type: "reject",
                titleTemplate: "Duplicate Email validation error",
                messageTemplate: "Email addresses must be unique"
            });