Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Can a Formula in Cell X modify Cell Y? | Excel Discussion (Misc queries) | |||
looking for a formula | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |