+ Reply to Thread
Results 1 to 6 of 6

Data Validation: Multi-Column Case-Sensitive Duplicates

  1. #1
    Registered User
    Join Date
    05-17-2013
    Location
    St. Petersburg
    MS-Off Ver
    Excel 2010
    Posts
    74

    Data Validation: Multi-Column Case-Sensitive Duplicates

    Hi,

    How can I prevent users creating multi-column duplicates? For example, in:

    John | Doe
    john | doe
    John | Kelly
    Richard | Doe
    John | Doe

    Excel should balk at the last row only. Can it be done without concatenating the data in a helper column?
    Last edited by Saighead; 02-16-2021 at 07:31 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) V 2210
    Posts
    35,756

    Re: Data Validation: Multi-Column Case-Sensitive Duplicates

    Assuming that your data start in a2 & B2 use:

    =SUMPRODUCT(--(EXACT($A$2:A2,A2))*--(EXACT($B$2:B2,B2))*($A$2:A2<>""))>1

    as the CF formula.
    Glenn



  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) V 2210
    Posts
    35,756

    Re: Data Validation: Multi-Column Case-Sensitive Duplicates

    Nope. Scrap that..

    =SUMPRODUCT(--(EXACT($A$2:$A2&$B$2:$B2,$A2&$B2))*($A$2:$A2<>""))>1

    instead... applied from A2 to B20, here.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-17-2013
    Location
    St. Petersburg
    MS-Off Ver
    Excel 2010
    Posts
    74

    Re: Data Validation: Multi-Column Case-Sensitive Duplicates

    Thanks a lot, that takes me halfway there. Could this formula be reworked somehow for data validation, not CF?
    Last edited by Saighead; 02-17-2021 at 11:47 AM.

  5. #5
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,590

    Re: Data Validation: Multi-Column Case-Sensitive Duplicates

    For DV, with A2 active cell, using Custom option:

    =SUMPRODUCT(--EXACT($A$2:$A2&"^"&$B$2:$B2,$A2&"^"&$B2))=1
    check Ignore Blanks

    then copy the DV across range A2:Bx

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) V 2210
    Posts
    35,756

    Re: Data Validation: Multi-Column Case-Sensitive Duplicates

    Unless I am mistaken... this is a rare occasion. I can correct XLent!!

    Your formula disallows an entry of John doe.

    What is required ( I think...) is:

    =SUMPRODUCT(--EXACT($A$1:$A1&"^"&$B$1:$B1,$A2&"^"&$B2))=0
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 02-16-2019, 07:05 PM
  2. Multi-select from data validation isn't working with auto-assigning data validation
    By iPenguin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-11-2017, 12:37 PM
  3. Remove Duplicates in Multi Column List Box
    By AmirSoft in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-11-2016, 11:02 AM
  4. Replies: 11
    Last Post: 02-05-2015, 07:13 PM
  5. Using Defined Names with Data Validation Depend and Data Validation Multi Select
    By Vinnie Chan in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-01-2012, 05:36 PM
  6. Find (case and diacritics insensitive) duplicates in one column
    By greek in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-15-2011, 04:00 AM
  7. [SOLVED] Data Validation Multi-column Dropdown
    By Bill Sturdevant in forum Excel General
    Replies: 2
    Last Post: 02-04-2005, 02:06 PM

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