+ Reply to Thread
Results 1 to 4 of 4

Data Validation: Unique combination of number and type from another cell

  1. #1
    Registered User
    Join Date
    03-13-2013
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    4

    Data Validation: Unique combination of number and type from another cell

    I have two columns: “D” with type (list with two types: TYPE1 and TYPE2) and “E” in which I will enter numbers.

    In my case it is possible to have a row with values in D: “TYPE1” and E: “123” and another row with values in D: “TYPE2” and E: “123” (same number as previous row in column E). I can’t have another row with the same combination of these values.

    So for any type, each number can be used only one time in the table.

    So, what I want to achieve is to validate the “E” column in order to allow only one combination of type and number !
    Can I achieve this with data validation ?

    I tried something like this in validation of the E Column but it’s not working:

    and((COUNTIF($E1:E20;E1)=1);(D1=type)) where type=the D column range.

    Thanks in advance

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Data Validation: Unique combination of number and type from another cell

    Try this...

    Let's assume the range to validate is E2:E5.

    Select the entire range E2:E5 starting from cell E2.

    Goto Data>Validation
    Allow: Custom
    Formula:

    =SUMPRODUCT(--(D$2:D$5=D2),--(E$2:E$5=E2))<2
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    03-13-2013
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Data Validation: Unique combination of number and type from another cell

    Thanks. It works !

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Data Validation: Unique combination of number and type from another cell

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1