Formula to identify cell with different value in list
 

I need a formula that will compare all values in a row of 50 fields and
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 catch
is that the field that is different will not always appear in the same
column on each row in the table. The only alternative I could come up
with was to use nested IF's, however, this is cumbersome due to the
number of fields that need to be evaluated.


--
carlossaltz
------------------------------------------------------------------------
carlossaltz's Profile: http://www.excelforum.com/member.php...o&userid=24205
View this thread: http://www.excelforum.com/showthread...hreadid=378205

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:A X1<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"
wrote in message
...

I need a formula that will compare all values in a row of 50 fields and
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 catch
is that the field that is different will not always appear in the same
column on each row in the table. The only alternative I could come up
with was to use nested IF's, however, this is cumbersome due to the
number of fields that need to be evaluated.


--
carlossaltz
------------------------------------------------------------------------
carlossaltz's Profile:
http://www.excelforum.com/member.php...o&userid=24205
View this thread: http://www.excelforum.com/showthread...hreadid=378205

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

"Biff" wrote in message
...
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:A X1<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"
wrote in message
...

I need a formula that will compare all values in a row of 50 fields and
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 catch
is that the field that is different will not always appear in the same
column on each row in the table. The only alternative I could come up
with was to use nested IF's, however, this is cumbersome due to the
number of fields that need to be evaluated.


--
carlossaltz
------------------------------------------------------------------------
carlossaltz's Profile:
http://www.excelforum.com/member.php...o&userid=24205
View this thread:
http://www.excelforum.com/showthread...hreadid=378205

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,BB16:DA16<"",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.


--
carlossaltz
------------------------------------------------------------------------
carlossaltz's Profile: http://www.excelforum.com/member.php...o&userid=24205
View this thread: http://www.excelforum.com/showthread...hreadid=378205

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 (BB16:DA16<"") 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,BB16:DA16,0)


In article ,
carlossaltz
wrote:


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,BB16:DA16<"",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.

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.


--
carlossaltz
------------------------------------------------------------------------
carlossaltz's Profile: http://www.excelforum.com/member.php...o&userid=24205
View this thread: http://www.excelforum.com/showthread...hreadid=378205

carlossaltz wrote:
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.

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


--
carlossaltz
------------------------------------------------------------------------
carlossaltz's Profile: http://www.excelforum.com/member.php...o&userid=24205
View this thread: http://www.excelforum.com/showthread...hreadid=378205

Look up array formula in help

--
Regards,

Peo Sjoblom

(No private emails please)


"carlossaltz"
wrote in message
...

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


--
carlossaltz
------------------------------------------------------------------------
carlossaltz's Profile:
http://www.excelforum.com/member.php...o&userid=24205
View this thread: http://www.excelforum.com/showthread...hreadid=378205