Home 
Search 
Today's Posts 
#1




compare data / MATCH(TRUE.. ROW..
hi, may be different way to do this from what using, but:
(2nd way tried to use range AA:AC, did not seem to work either; last resort would be to use item below that works, in 2 different cond. format slots). thanks. trying to test for same data, in same column, and in 2nd column portion (at of document/sheet) where I might have a new NAME entered at top without a suffix: .xx or .yy, I am trying to compare it to same NAME at end that might have a suffix. at end of document, in 2nd column (AC1150 in this case) using: =IF(OR(RIGHT(AA1150,3)={".xx",".yy"}),LEFT(AA1150, LEN(AA1150)3),"na") Test is just for a conditional format, can test 1 column & following works: =IF(ROW($A131)ROW($A$1100),"",OR( INDEX(ROW($AC$1100:$AC$1160),MATCH(TRUE,$AC$1100:$ AC$1160=$AA131,0))ROW($A$1100))) Where trying to OR() in the 2nd example does not work: =IF(ROW($A131)ROW($A$1100),"",OR( INDEX(ROW($AA$1100:$AA$1160),MATCH(TRUE,$AA$1100:$ AA$1160=$AA131,0))ROW($A$1100), INDEX(ROW($AC$1100:$AC$1160),MATCH(TRUE,$AC$1100:$ AC$1160=$AA131,0))ROW($A$1100))) 
#2




compare data / MATCH(TRUE.. ROW..
do not know for sure what you are trying to do
row(A$131) will always be less than row($A$1100) Row($A$1100) will always = 1100 Match(true,$AA$1100:$AA$1160,$AA131,0) is the same as Match($AA131,$AA1100:$AA$1160,0) INDEX(ROW($AA$1100:$AA$1160),MATCH(TRUE,$AA$1100:$ AA$1160=$AA131,0))ROW($A$1100), is the same as Match($AA131,$AA1100:$AA$1160,0)1 I don't think this is what you are trying to do "nastech" wrote: hi, may be different way to do this from what using, but: (2nd way tried to use range AA:AC, did not seem to work either; last resort would be to use item below that works, in 2 different cond. format slots). thanks. trying to test for same data, in same column, and in 2nd column portion (at of document/sheet) where I might have a new NAME entered at top without a suffix: .xx or .yy, I am trying to compare it to same NAME at end that might have a suffix. at end of document, in 2nd column (AC1150 in this case) using: =IF(OR(RIGHT(AA1150,3)={".xx",".yy"}),LEFT(AA1150, LEN(AA1150)3),"na") Test is just for a conditional format, can test 1 column & following works: =IF(ROW($A131)ROW($A$1100),"",OR( INDEX(ROW($AC$1100:$AC$1160),MATCH(TRUE,$AC$1100:$ AC$1160=$AA131,0))ROW($A$1100))) Where trying to OR() in the 2nd example does not work: =IF(ROW($A131)ROW($A$1100),"",OR( INDEX(ROW($AA$1100:$AA$1160),MATCH(TRUE,$AA$1100:$ AA$1160=$AA131,0))ROW($A$1100), INDEX(ROW($AC$1100:$AC$1160),MATCH(TRUE,$AC$1100:$ AC$1160=$AA131,0))ROW($A$1100))) 
#3




compare data / MATCH(TRUE.. ROW..
Hi, I am not sure how to explain directly either, had help with a hyperlink
formula that utilized this string. Have: column of names that have an extension applied at one site, however the next site does not use extensions. In trying to make a list at end of document between precise (absolute) rows for a "NO" List, I was trying to or the string below (i.e.: the same item to compare in two columns.. not quite adjacent); where using a single copy of the string gets the correct answer (in conditional formatting/ tested & got True result): it gets a null result using the same string twice / addressing 2 different columns, where they work singularly). would think the answer would have been instead of, rough e.g.: =IF(OR(RIGHT($A$50:$A100,3)={".xy",".xz"},RIGHT($C $50:$C$100)={".xy",".xz"}),pos,neg) does not work, don't know why; but: came up with workaround to but all results in one column / just test one column: =IF(OR(RIGHT(AA1132,3)={".pk",".ob"}),LEFT(AA1132, LEN(AA1132)3),AA1132) am curious what doing wrong in first example, but won't lose any sleep... thanks Is that what you were saying "bj" wrote: do not know for sure what you are trying to do row(A$131) will always be less than row($A$1100) Row($A$1100) will always = 1100 Match(true,$AA$1100:$AA$1160,$AA131,0) is the same as Match($AA131,$AA1100:$AA$1160,0) INDEX(ROW($AA$1100:$AA$1160),MATCH(TRUE,$AA$1100:$ AA$1160=$AA131,0))ROW($A$1100), is the same as Match($AA131,$AA1100:$AA$1160,0)1 I don't think this is what you are trying to do "nastech" wrote: hi, may be different way to do this from what using, but: (2nd way tried to use range AA:AC, did not seem to work either; last resort would be to use item below that works, in 2 different cond. format slots). thanks. trying to test for same data, in same column, and in 2nd column portion (at of document/sheet) where I might have a new NAME entered at top without a suffix: .xx or .yy, I am trying to compare it to same NAME at end that might have a suffix. at end of document, in 2nd column (AC1150 in this case) using: =IF(OR(RIGHT(AA1150,3)={".xx",".yy"}),LEFT(AA1150, LEN(AA1150)3),"na") Test is just for a conditional format, can test 1 column & following works: =IF(ROW($A131)ROW($A$1100),"",OR( INDEX(ROW($AC$1100:$AC$1160),MATCH(TRUE,$AC$1100:$ AC$1160=$AA131,0))ROW($A$1100))) Where trying to OR() in the 2nd example does not work: =IF(ROW($A131)ROW($A$1100),"",OR( INDEX(ROW($AA$1100:$AA$1160),MATCH(TRUE,$AA$1100:$ AA$1160=$AA131,0))ROW($A$1100), INDEX(ROW($AC$1100:$AC$1160),MATCH(TRUE,$AC$1100:$ AC$1160=$AA131,0))ROW($A$1100))) 
#4




compare data / MATCH(TRUE.. ROW..
I am submitting a new problem if you want a crack at it: trying to use a
worker column to add many sort parameters. Problem is getting a number column to sort in descending order (i.e. make a negative number so an "Ascending" sort will handle the other parameters as ascending as well). =IF($Z$2=1,AA9,9)&IF($CU$2=1,CU9*10^11,9) have used variations with TEXT(CU9,"00000000000") and trying to put the negative sign around different spots, not getting to work on a large project. thanks. "bj" wrote: do not know for sure what you are trying to do row(A$131) will always be less than row($A$1100) Row($A$1100) will always = 1100 Match(true,$AA$1100:$AA$1160,$AA131,0) is the same as Match($AA131,$AA1100:$AA$1160,0) INDEX(ROW($AA$1100:$AA$1160),MATCH(TRUE,$AA$1100:$ AA$1160=$AA131,0))ROW($A$1100), is the same as Match($AA131,$AA1100:$AA$1160,0)1 I don't think this is what you are trying to do "nastech" wrote: hi, may be different way to do this from what using, but: (2nd way tried to use range AA:AC, did not seem to work either; last resort would be to use item below that works, in 2 different cond. format slots). thanks. trying to test for same data, in same column, and in 2nd column portion (at of document/sheet) where I might have a new NAME entered at top without a suffix: .xx or .yy, I am trying to compare it to same NAME at end that might have a suffix. at end of document, in 2nd column (AC1150 in this case) using: =IF(OR(RIGHT(AA1150,3)={".xx",".yy"}),LEFT(AA1150, LEN(AA1150)3),"na") Test is just for a conditional format, can test 1 column & following works: =IF(ROW($A131)ROW($A$1100),"",OR( INDEX(ROW($AC$1100:$AC$1160),MATCH(TRUE,$AC$1100:$ AC$1160=$AA131,0))ROW($A$1100))) Where trying to OR() in the 2nd example does not work: =IF(ROW($A131)ROW($A$1100),"",OR( INDEX(ROW($AA$1100:$AA$1160),MATCH(TRUE,$AA$1100:$ AA$1160=$AA131,0))ROW($A$1100), INDEX(ROW($AC$1100:$AC$1160),MATCH(TRUE,$AC$1100:$ AC$1160=$AA131,0))ROW($A$1100))) 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
compare & match text  Excel Worksheet Functions  
Compare and Match Functions  Excel Discussion (Misc queries)  
Help me, compare 2 sheet and extract the match data into the new sheet.  Excel Discussion (Misc queries)  
Want to compare two columns and then fill with data from the match  Excel Worksheet Functions  
Match a1 to B1:B10, = true  Excel Discussion (Misc queries) 