Compare Text in Array of Text

  • Thread starter Thread starter Philippe L. Balmanno
  • Start date Start date
P

Philippe L. Balmanno

Hi, I need some help in modifying my formula please.

I have two arrays; both contain 9col X 51row. The first array (A2:I51)
contain words or empty spaces. The second array (A54:I104) refers to
a cell in the first array, determines if it is empty and then compares
the text in that cell to the rest of the first array to find if that
word is elswhere in the array. The problem is that there are only two
things that return "Empty" or TRUE and I know that some should be
FALSE. I believe it is because the cell compares itself which results
in TRUE. How would I re-write this to exclude looking at itself so
that I can just copy the formula across and down?

In second array each cell has a formula
=IF(A2="","Empty",OR(EXACT(A2,$A$2:$I$51)))

TIA

Phil
 
Alok Joshi wrote...
How about using the following formula
=IF(A2="","Empty",(COUNTIF($A$2:$I$51,A2)>1))
Click to expand...

Alok's formula works if the OP is willing to use case-insensitive
matching. Otherwise, the OP needs something like

=IF(A2="","Empty",SUMPRODUCT(--EXACT($A$2:$A$51,A2))>1)
 
Alok Joshi wrote...

Alok's formula works if the OP is willing to use case-insensitive
matching. Otherwise, the OP needs something like

=IF(A2="","Empty",SUMPRODUCT(--EXACT($A$2:$A$51,A2))>1)
Click to expand...

Thanks for your response I entered as is and then with
ctrl+shift+enter both resulted in FALSE and Empty when there are
matches.

Thanks again
 
How about using the following formula
=IF(A2="","Empty",(COUNTIF($A$2:$I$51,A2)>1))
Click to expand...
Thanks for your response I entered as is and then with
ctrl+shift+enter both resulted in TRUE and Empty when there are
no matches.

Thanks again
 
Philippe L. Balmanno wrote...
...
Thanks for your response I entered as is and then with
ctrl+shift+enter both resulted in FALSE and Empty when there
are matches.
Click to expand...

Probably because I screwed up the range address in my formula. Try th
following which does *not* need to be entered as an array formula.

=IF(A2="","Empty",SUMPRODUCT(--EXACT($A$2:$I$51,A2))>1)

I tested this with A2:I51 filled with the formula

=IF(RAND()<0.1,"",CHAR(IF(RAND()<0.5,65,97)+26*RAND()))

and it returns TRUE, FALSE and Empty depending on changing cell values
 
Philippe L. Balmanno wrote...
..

Probably because I screwed up the range address in my formula. Try the
following which does *not* need to be entered as an array formula.

=IF(A2="","Empty",SUMPRODUCT(--EXACT($A$2:$I$51,A2))>1)

I tested this with A2:I51 filled with the formula

=IF(RAND()<0.1,"",CHAR(IF(RAND()<0.5,65,97)+26*RAND()))

and it returns TRUE, FALSE and Empty depending on changing cell values.
Click to expand...

Perfect! Thank you so much.
 
Back
Top