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)))

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)))

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)))

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)))