# Thread: Find if a cell value Repeats in variable range

1. 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?

2. 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. [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
•