Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm trying to create a formula that finds repeats in cells that are text values in a variable rage within a column or row.
    I have the following formula which works fine for a fixed range of values.

    [codebox]=IF(SUM(IF(FREQUENCY(MATCH(A2:A10,A2:A10,0),MATCH( A2:A10,A2:A10,0))>0,1))<COUNTA(A2:A10),"REPEAT!"," No repeats")[/codebox]

    If, for example, A2 through A10 had the following values in them:

    pen
    chair
    desk
    keys
    apple
    mouse
    paper
    cup
    glass

    It returns "No repeats". However, if A10 is blank, it returns "#NA".

    I want it to still work if the ending cells at the end are blank. I've attached a sheet with the formula and some values. Any ideas?
    Attached Files Attached Files

  2. #2
    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
    How about the Array formula (confirm with ctrl-shift-enter):

    =IF(OR(COUNTIF(A2:A15,A2:A15)>1),"REPEAT!","No repeats")

    Countif works with blanks where frequency does not...
    Steve

  3. #3
    New Lounger
    Join Date
    May 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='780678' date='19-Jun-2009 14:36']How about the Array formula (confirm with ctrl-shift-enter):

    =IF(OR(COUNTIF(A2:A15,A2:A15)>1),"REPEAT!","No repeats")

    Countif works with blanks where frequency does not...
    Steve[/quote]

    Genius. Thanks!

Posting Permissions

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