Unique Value Validation Rule

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

    Posted 02 Aug 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: {
    from:"AND(COUNTIF(A:A,A3)=1)",
    dataType: "custom",
    messageTemplate: "Email must be unique",
    type: "reject"
    }

  2. Answer
    Stefan
    Admin
    Stefan avatar
    265 posts

    Posted 04 Aug 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:

    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
     
    Get started with Kendo UI in days. Online training courses help you quickly implement components into your apps.
     
  3. Kendo UI is VS 2017 Ready
  4. Erik
    Erik avatar
    5 posts
    Member since:
    Feb 2015

    Posted 11 Aug 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");
    emailRange.validation({
                    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