Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Mar 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Problems (Excel 2000)

    I'm having trouble with a silly formula. I want to be able to enter names on a list -- and know if I've repeated them previously. Right now I can't get the formula to look at a range -- it keeps taking the top value. I've written it two ways and neither quite work. Any help would be appreciated!

    =EXACT(A8,$A$2:A7)
    =IF(A14=$A$2:A13,"No","Yes")

    I've thought about LOOKUP but I'm not sure how to use this. Seems like it should be simple!
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Problems (Excel 2000)

    It seems that you want to check whether a given entry occurs more than once in exactly the same shape. If so,

    in A2 enter and copy down:

    =SUMPRODUCT((EXACT($A$2:$A$10,A2))+0)
    Microsoft MVP - Excel

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

    Re: Formula Problems (Excel 2000)

    See if this does what you want:

    <pre>=IF(ISERROR(VLOOKUP(A14,A1:A13,1,FALSE)),"No" ,"Yes")
    </pre>

    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Problems (Excel 2000)

    If that's what is wanted, a COUNTIF formula would suffice:

    =IF(COUNTIF(A1:A13,A14),"Yes","No")

    or just

    =COUNTIF(A1:A13,A4)

    where the cell of the formula can be custom formatted as:

    [=0]"No";"Yes"


    Aladin
    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: Formula Problems (Excel 2000)

    To prevent duplicate entries in a list I use Exce's Data->Validation option.

    Suppose your list range is E13:E62

    Put cellpointer In cell [e13],
    Select Data from the top panel command bar and then select Validation from the menu dropdown.
    On the sheet tab labeeled 'Setting:
    set 'Allow:' dropdown to 'Custom'
    check the box labelled 'Ignore blank'
    In the Formula: section type
    =COUNTIF($E$13:$E$62,E13)<2
    (Note dollar signs are important!)
    Click the tab heading 'Error Alert'
    Check the box labelled 'Show erro alert after invalid data is entered'
    For the 'Style' dropdown, pick 'Stop'
    For the 'Title', enter something like:
    Duplicate entry found!
    For the 'Error message:' entry, type something like
    This entry has already been entered in the list!
    Only one entry allowed.

    Now copy the cell and use
    Edit - PasteSpecial - Validation
    to copy to the list entry range.

    That's it!
    Now, whenever you try to enter something that has already been entered anywhere in the specified list range, you will see the error message and the new entry will be disallowed.

    zeddy

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Problems (Excel 2000)

    I believe you confounded me with the original poster (OP).

    It's of course a good idea if that's what the OP wants.

    Aladin
    Microsoft MVP - Excel

  7. #7
    Lounger
    Join Date
    Mar 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Problems (Excel 2000)

    Thank you all for your suggestions and your help! The formula that Legare Coleman gave me is the one that gave me the desired result. I wanted to know if something had been typed previously -- was what I typed a new client or an existing one. That formula gave me that information -- thank you, thank you! Now I just have to figure out why it does that! :-)

Posting Permissions

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