Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Checking for duplicate/similar values

    Hi loungers....I need some help adjusting a formula that I am using to help identify values that are duplicate (or similar). I am attaching a sample file. In it, there is conditional formatting in C8:C19 that will turn a cell white/red if the number 1 appears in N8:N19.......the appearance of the number 1 in column N occurs by virtue of the array formula in N8:N19 (this is a formula that Hans gave me quite some time ago in another context). In this sample file, you will see that there are some 'duplicate' values (3, 3*..or Blue, Blue***).......these aren't exactly duplicate values, but I want the formula in column N to disregard the * (so that Blue*** is the same as Blue, or 4** is the same as 4, so that it will trigger the color change in column C).

    I tried inserting a SUBSTITUTE formula into the formula in column N (hoping it would overlook the * and 'see' Blue*** as Blue, or 3** as 3 etc etc) but can't get it to work.....any suggestions? And, maybe the solution lies in how I arrange the data in column Q, but I can't see how....any help would be appreciated.Thank you
    Attached Files Attached Files

  2. #2
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    LOUNGERS...I received the following reply (and attachment) from Hans at Eileens' Lounge...........

    Re: Checking for duplicate/similar values

    Postby HansV 12 Apr 2013, 06:24
    You can use the following array formula confirmed with Ctrl+Shift+Enter in N8:

    =IF(MAX(COUNTIF(D8:M8,Q$8:Q$16&"*"))>1,1,"")

    Fill down to N19.

    For this to work, all values in D8:M19 should be text values. You can turn the numbers 1, 2, etc. into text values by prefixing them with an apostrophe '.
    To turn new entries into text automatically, format D8:M19 as Text.

    See the attached version.
    Attached Files Attached Files

Posting Permissions

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