+ Reply to Thread
Results 1 to 4 of 4

Data Validation with wildcards and countif/sumproduct to prevent multiple entries

  1. #1
    Registered User
    Join Date
    10-18-2010
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2003
    Posts
    3

    Data Validation with wildcards and countif/sumproduct to prevent multiple entries

    Hello I am currently trying to use Excel 2003 Data validation to prevent duplicate data Entry.

    The data I have for example in A1:A4 is as follows

    04-20-026-22W4/2
    16-33-025-22W4/2
    07-27-026-22W4/0
    04-20-026-22W4/0

    In the data validation I have custom and this for the formula

    =COUNTIF($A$1:$A$4,A1)<=1 for cell A1

    My problem arises in that I need it to do this lookup based on the wildcard for the last number.

    So that cell A1 = A4 and therefore will not let me put in 04-20-026-22W4/0 or /2 or anything else.

    So something similar to 04-20-026-22W4/* where * = wildcard.

    The cells are currently formatted as General and I have tried a few variations of countif and sumproduct with no luck.

    Thanks in advance for any help you can offer.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Data Validation with wildcards and countif/sumproduct to prevent multiple entries

    If the strings are always the same length then:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-18-2010
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Data Validation with wildcards and countif/sumproduct to prevent multiple entries

    Quote Originally Posted by DonkeyOte View Post
    If the strings are always the same length then:

    Please Login or Register  to view this content.
    Perfect that is what I was looking for, I just needed to add the &.

    Thanks a lot for the help and very quick response.

    Have a nice day.

  4. #4
    Registered User
    Join Date
    10-18-2010
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2003
    Posts
    3

    [SOLVED] Data Validation with wildcards and countif to prevent multiple entries

    This message has been solved.

+ 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