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

    Finding duplicate values

    Hi all....I have started on a set of formulas that are designed to find duplicate values and I am attaching the sample workbook. I want to locate duplicate values in a row, and then use cond formatting to color the date red if there are duplicates. I am triggering the conditional formatting by using a formula that puts an "&" in an adjacent column.....that formula is an array formula that I found here (from Hans V) but it only works if there is a value in cell d10.....if the duplicates are in, say, cells e10 and g10, then the formula doesn't seem to work.....any ideas on why?
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Attached is an example of CountIf formulas that will check each value in the row for a duplicate. It will not check other rows.

    Since you are only looking at one row the formulas are not Array formulas.

    Of course you could combine the formulas into one large formula but I leave that up to you.

    Hope this helps

    DuthieT
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Duthiet....thank you for the assistance....i"m attaching an updated file (duplicates3.xls)....what I am trying to do it find a formula for, say, B4 that will scan D4:L4 and look for duplicate values from the list of values in N4:n49...if it finds a duplicate, it returns a 1 in B4 (and that will trigger a format change to C4).....I have used 3 different formulas in B4, B5, B6 in an effort to find the correct one....the B4 formula (a shortened version that only looks at N4:n6) works, but I doubt that I can have 44 "OR"s nested in one formula.....any suggestions?
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In B4 enter the Array formula (confirm with ctrl-shift-enter):
    =1*(SUM(ISNUMBER(MATCH(D4:L4,$N$4:$N$49,0))*1)>0)

    Steve
    PS: If you change you conditional format to just = B4 then you can simplify the formula to:
    =SUM(ISNUMBER(MATCH(D4:L4,$N$4:$N$49,0))*1)>0
    Last edited by sdckapr; 2011-05-19 at 13:32. Reason: added ps

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve...I tried that but it doesn't work.....??..??....do you want me to repost the sample with your code?

  6. #6
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve....I adjusted the formula in B4 to be an array formula of =IF(MAX(COUNTIF(d$4:l$4,n$4:N$49))>1,1,"") and that returns a 1 if there is a duplicate value in that row, which triggers a format change in the dates....seems to be working..thanks again for pointing me in the right direction

Posting Permissions

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