Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Aug 2002
    Location
    Michigan, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Look up/prevent duplicates in a column (2002)

    Hi, thanks for reading, I would like to illustrate an example. I have one column and say 5 rows. On each row is a combo box the user can click, and choose from "Vanilla", "Chocolate" and "Strawberry" for example. How can I prevent the user from selecting a value that has already been used? For example, let's say in the first row the user picked "Vanilla." Now, in the second row the user should NOT be able to pick Vanilla, and only "Chocolate" or "Strawberry" would be allowed. Is there an easy way to do this...?????? I've tried VLookUP, Match, etc. but I'm at a loss now...

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look up/prevent duplicates in a column (2002)

    Use data, validation for that. Use the custom type and For the formula use

    =COUNTIF($A$1:$A$5,"=" & A1)<=1

    (cell A1 has to be active when defining the validation and the list extends to cell A5)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Aug 2002
    Location
    Michigan, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look up/prevent duplicates in a column (2002)

    Thank you kindly for your reply! Ummm, I hate to admit this, but I already used Data Validation to limit the input in each cell to "List." Is there any other work around that you can think of? Thanks again for your help, I appreciate it! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look up/prevent duplicates in a column (2002)

    You could probably use the worksheet change event routine. If one of those 5 cells is changed, then modify the list of what displays in the drop down list so that used values can't be chosen for the other cells.
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look up/prevent duplicates in a column (2002)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    This maybe of interest to you...

    http://www.contextures.com/xlDataVal03.html

  6. #6
    Star Lounger
    Join Date
    Aug 2002
    Location
    Michigan, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look up/prevent duplicates in a column (2002)

    Thanks again for everyone's help. Maxflia10, now THAT is quite a find. It took me hours to finally map it to my spreadsheet, and I finally have it working. Thanks for sharing this link. By the way, this is the most AMAZING excel formula I personally have ever seen in my life - the complexity and "trickery" is just pure genius... Problem Solved 95%. Why not 100%? Because in some circumstances I actually need to allow a duplicate "N/A" entry to be selected multiple times (and thus do not want it to disappear), but I'm sure I can tweak it later...

    THANK YOU!!!!!!!!

Posting Permissions

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