Unique Value Validation Rule

3 posts, 1 answers
  1. Erik
    Erik avatar
    5 posts
    Member since:
    Feb 2015

    Posted 02 Aug 2016 Link to this post

    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: {
    dataType: "custom",
    messageTemplate: "Email must be unique",
    type: "reject"

  2. Answer
    Stefan avatar
    2915 posts

    Posted 04 Aug 2016 Link to this post

    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:


    I hope this is helpful.

    Telerik by Progress
    Get started with Kendo UI in days. Online training courses help you quickly implement components into your apps.
  3. Erik
    Erik avatar
    5 posts
    Member since:
    Feb 2015

    Posted 11 Aug 2016 in reply to Stefan Link to this post

    Thanks Stefan, indeed, this worked for me. 

    The code was: 

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

Back to Top