Formula to identify cell with different value in list

[carlossaltz]

I need a formula that will compare all values in a row of 50 fields an
return the cell reference of the field that is different from the rest
Of the 50 entries, 49 are similar and the 50th is different. The catc
is that the field that is different will not always appear in the sam
column on each row in the table. The only alternative I could come u
with was to use nested IF's, however, this is cumbersome due to th
number of fields that need to be evaluated

 

[Biff]

Hi!

cell reference

Does that mean cell address?

Assume your values are in the range A1:AX1

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=IF(COUNTIF(A1:AX1,A1)>1,ADDRESS(1,MATCH(TRUE,A1:AX1<>A1,0)),"$A$1")

If this formula is going to be in the same row as the table row you want to
search, use:

=IF(COUNTIF(A1:AX1,A1)>1,ADDRESS(ROW(),MATCH(TRUE,A1:AX1<>A1,0)),"$A$1")

Biff

"carlossaltz" <[email protected]>
wrote in message

 

[Biff]

Slight adjustment:

If this formula is going to be in the same row as the table row you want
to search, use:

=IF(COUNTIF(A1:AX1,A1)>1,ADDRESS(ROW(),MATCH(TRUE,A1:AX1<>A1,0)),"$A$1")

Should be:

=IF(COUNTIF(A1:AX1,A1)>1,ADDRESS(ROW(),MATCH(TRUE,A1:AX1<>A1,0)),"$A$"&ROW())

Biff

 

[carlossaltz]

Thanks for your help. How would this formula change if I am looking for
a false value within a row full of true values. In this instance, I
have a row full of "if" formulas which read as follows:

=IF(ISERROR(FIND(DA$6,UPPER($D16))),"",DA$6) (In my spreadsheet, the
DA$6 fluctuates from BB$6 through to DA$6 on the row being evaluated.

All but one of the fields in this row will provide me with a FALSE
response. All others will return a TRUE response. I try using the
following statement to find the column reference of the one and only
FALSE statement:

=MATCH(TRUE,BB16A16<>"",0)

This formula is extracted from the formula you provided me. In the
Functions Argument dialog box, the result of this formula displays
correctly. However, the result that displays in the cell is the error
message "#VALUE!".

What do you think I can be doing wrong? Thanks again for your help.

 

[JE McGimpsey]

I'm a bit confused:

All but one of the fields in this row will provide me with a FALSE
response.

sounds like only one value will be TRUE, but

I try using the following statement to find the column reference of
the one and only FALSE statement:

says only one value will be FALSE. And your formula, when array-entered,
creates a boolean array (BB16A16<>"") based on whether the cell is
blank, not the value in the cells themselves. It will find the first
non-blank cell.

If you really mean that only one value in the range will be FALSE, and
you want to find it, use

=MATCH(FALSE,BB16A16,0)

 

[carlossaltz]

I tried your formula, but in this case I get a "#N/A" result in the cel
and the result that appears in the Function Arguments dialog box i
blank. When I state the formula the way it appears in my post, th
result in the Function Arguments dialog box is correct, even though th
result in the cell is "#VALUE!".

It's confusing for me, too, but that's what's happening in m
spreadsheet.

By the way, the values that are being used to verify if the statemen
in each field is true or false is a text string not a numeric value.
In the formula "=IF(ISERROR(FIND(DA$6,UPPER($D16))),"",DA$6)", DA$
refers to a text string. I don't know if this can affect the outcom
of the formula, but I thought I'd mention it

 

[Aladin Akyurek]

I tried your formula, but in this case I get a "#N/A" result in the cell
and the result that appears in the Function Arguments dialog box is
blank. When I state the formula the way it appears in my post, the
result in the Function Arguments dialog box is correct, even though the
result in the cell is "#VALUE!".

It's confusing for me, too, but that's what's happening in my
spreadsheet.

By the way, the values that are being used to verify if the statement
in each field is true or false is a text string not a numeric value.
In the formula "=IF(ISERROR(FIND(DA$6,UPPER($D16))),"",DA$6)", DA$6
refers to a text string. I don't know if this can affect the outcome
of the formula, but I thought I'd mention it.

You need to confirm such a formula with control+shift+enter, not just
with enter.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[carlossaltz]

Thanks for your help. The control-shift-enter sequence did it. Woul
you please explain to me what exactly does this sequence accomplish

 

[Peo Sjoblom]

Look up array formula in help

--
Regards,

Peo Sjoblom

(No private emails please)


"carlossaltz" <[email protected]>
wrote in message
news:[email protected]...