Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 27th 07, 08:38 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 383
Default 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   Report Post  
Old June 27th 07, 10:26 PM posted to microsoft.public.excel.misc
bj bj is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,397
Default 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   Report Post  
Old July 2nd 07, 09:12 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 383
Default 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   Report Post  
Old July 2nd 07, 09:16 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 383
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
compare & match text Susanne Excel Worksheet Functions 5 May 22nd 07 05:34 PM
Compare and Match Functions DP63 Excel Discussion (Misc queries) 1 June 20th 06 04:00 PM
Help me, compare 2 sheet and extract the match data into the new sheet. sweetnet Excel Discussion (Misc queries) 1 February 22nd 06 07:49 PM
Want to compare two columns and then fill with data from the match Needy C Excel Worksheet Functions 1 January 11th 06 04:52 AM
Match a1 to B1:B10, = true Kikkoman Excel Discussion (Misc queries) 4 August 30th 05 02:08 PM


All times are GMT +1. The time now is 09:19 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017