+ Reply to Thread
Results 1 to 7 of 7

Using Data Validation to stop duplicates across mulitple non consecutive columns

  1. #1
    Registered User
    Join Date
    06-07-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Using Data Validation to stop duplicates across mulitple non consecutive columns

    Hello, Can someone help me with creating a data validation rule to stop duplicates across selected columns in a worksheet.
    I need to create unique numbers in certain columns. Other columns will be allowed duplicates. For example I will need to prevent any duplicates occurring in Columns F,I, L and O wherever there is data entered.
    I have been successful in creating the COUNTIF rule for a range of numbers but not if the columns are separated.
    Would be very grateful if someone can help me with this, many thanks

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using Data Validation to stop duplicates across mulitple non consecutive columns

    1) Highlight F1 and open the Data Validation window
    2) Apply these settings:

    Allow: Custom
    Formula: =COUNTIF($F:$F, F1)+COUNTIF($I:$I, F1)+COUNTIF($L:$L, F1)+COUNTIF($O:$O, F1)<2


    3) Now copy F1 and paste only on the cells down columns F, I, L and O that you require.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    06-07-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Using Data Validation to stop duplicates across mulitple non consecutive columns

    Thanks so much for your help Jerry, I did that but now I am unable to enter anything in those columns at all. Not even a letter. Have you got any other idea's please.
    thank you

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Using Data Validation to stop duplicates across mulitple non consecutive columns

    Hi Monica,

    See if using Conditional Formatting will help. If you put two numbers on the same row that are the same it will turn the whole row yellow. See the attached for an example.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using Data Validation to stop duplicates across mulitple non consecutive columns

    Sure, post your workbook where you've installed the Data Validation as I described above and I'll check your work.
    It worked on my test workbook, so something is different in yours.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  6. #6
    Registered User
    Join Date
    06-07-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Using Data Validation to stop duplicates across mulitple non consecutive columns

    Thanks heaps Jerry, It all works now. Have no idea what I was doing wrong yesterday but all good now. This will help me so much at work. Have a good day.

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using Data Validation to stop duplicates across mulitple non consecutive columns

    I've marked this thread as SOLVED for you.
    Next time, select Thread Tools from the links above and mark this thread as SOLVED. Thanks.

+ 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