Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi all....I have a coumn of data. I want to set up a Data Validation warning if one of those numbers is a duplicate of another. I am attaching a small sample; in column B, in rows 2-4 (yellow), I have tried to do it using a MATCH formula. In rows 5-7, I tried to do it using an IF formula....in either case, it always returns the Warning. As well, when entering the custom formula in the Data Validation, i have to do it cell-by-cell because it doesn't seem to change the first variable (B2 or B3 etc) automatically....ie: if I shade the entire column B and enter the Custom formula in Data Validation, it all relates to B2...........any ideas of wht I am missing? Thanks
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Select B2:B7, then use this formula in the Data Validation settings:
    =COUNTIF($B$2:$B2,$B2)=1
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    ....hi Rory...I don't understand your suggestion, b/c it puts the formula you suggested into each cell in column B, and only seems to relate B2 to the range (if that makes sense...it only compares B2 with other data in column B, even if I enter data in B3 or B4 or B5 etc etc

    I am looking for a formula in each cell (using Data Validation) that will check data entered into a cell in column B against all other existing data in column B and if there is a match, it will pop up the Data Validation warning...if there is no match (ie: if the data is unique) then the Data Validation warning pop-up does not appear.....

  4. #4
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Formula provided by rory will do the trick. Pl see the attached. The only drawback is it will not validate the data backward.
    Attached Files Attached Files
    Regards
    Prasad

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you Prasad....but surely there is a MATCH formula that can validate backward, and check the entire column?...or maybe I need a 'worksheet change event' type of formula?

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm assuming by "validate backwards" you mean that if you change a previously entered value such as B3 and it matches B5, you want the validation to fire. If that's the case, define the entire range. Rory's formula could be changed to: =COUNTIF($B$2:$B$7,$B2) or =COUNTIF($B:$B,$B2)=1 for the entire column

Posting Permissions

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