Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 work-around 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |