find concatenated values question
 

i have to find values based on 3 non-contiguous columns in another worksheet. i
can concatenate the string i'm looking for, but what's the best way to find that
string in 3 non-contiguous columns in another worksheet.

tried union as the range to search but couldn't get it to work.

just looking for ideas, no need to write any code.

thanks


--


Gary

find concatenated values question
 

forgot to mention an autofilter could be an option.

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i have to find values based on 3 non-contiguous columns in another worksheet. i
can concatenate the string i'm looking for, but what's the best way to find
that string in 3 non-contiguous columns in another worksheet.

tried union as the range to search but couldn't get it to work.

just looking for ideas, no need to write any code.

thanks


--


Gary

find concatenated values question
 

If I understand you, you have a value "NC123ABCD" and you want to find the
row in another worksheet where "NC" is in column 3, "123" in column 5 and
"ABCD" in column 8. Something like that?

Hmm, I've never tried anything like that. My first thought would be to
create a new column in the other worksheet in which each formula read
=RC3&RC5&RC8 (in the R1C1 notation, obviously) and then do a normal VLOOKUP
or some other kind of Find function using that column instead of trying to
match on the individual columns. Is that possible?

Well, sure it is; even if it's not your workbook and you're not allowed to
modify it, you can have your program open up a read-only copy of it, create
the extra column, use it and then throw it away when you're done; open it
read-only like that and Excel won't allow you to save the changes even
accidentally. How's that?

--- "Gary Keramidas" wrote:
i have to find values based on 3 non-contiguous columns in another worksheet. i
can concatenate the string i'm looking for, but what's the best way to find that
string in 3 non-contiguous columns in another worksheet.

tried union as the range to search but couldn't get it to work.

find concatenated values question
 

yes, a helper column is a possibility, thanks.

--


Gary


"Bob Bridges" wrote in message
...
If I understand you, you have a value "NC123ABCD" and you want to find the
row in another worksheet where "NC" is in column 3, "123" in column 5 and
"ABCD" in column 8. Something like that?

Hmm, I've never tried anything like that. My first thought would be to
create a new column in the other worksheet in which each formula read
=RC3&RC5&RC8 (in the R1C1 notation, obviously) and then do a normal VLOOKUP
or some other kind of Find function using that column instead of trying to
match on the individual columns. Is that possible?

Well, sure it is; even if it's not your workbook and you're not allowed to
modify it, you can have your program open up a read-only copy of it, create
the extra column, use it and then throw it away when you're done; open it
read-only like that and Excel won't allow you to save the changes even
accidentally. How's that?

--- "Gary Keramidas" wrote:
i have to find values based on 3 non-contiguous columns in another worksheet.
i
can concatenate the string i'm looking for, but what's the best way to find
that
string in 3 non-contiguous columns in another worksheet.

tried union as the range to search but couldn't get it to work.

find concatenated values question
 

How about using MATCH

On Error Resume Next
val = ActiveSheet.Evaluate("MATCH(1,(A2:A200=""" & value1 &
""")*(C2:C200=""" & value2 & """)*(E2:E200=""" & value3 & """),0)")
If Not IsError(val) Then

'....
End If


--
__________________________________
HTH

Bob

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
yes, a helper column is a possibility, thanks.

--


Gary


"Bob Bridges" wrote in message
...
If I understand you, you have a value "NC123ABCD" and you want to find
the
row in another worksheet where "NC" is in column 3, "123" in column 5 and
"ABCD" in column 8. Something like that?

Hmm, I've never tried anything like that. My first thought would be to
create a new column in the other worksheet in which each formula read
=RC3&RC5&RC8 (in the R1C1 notation, obviously) and then do a normal
VLOOKUP
or some other kind of Find function using that column instead of trying
to
match on the individual columns. Is that possible?

Well, sure it is; even if it's not your workbook and you're not allowed
to
modify it, you can have your program open up a read-only copy of it,
create
the extra column, use it and then throw it away when you're done; open it
read-only like that and Excel won't allow you to save the changes even
accidentally. How's that?

--- "Gary Keramidas" wrote:
i have to find values based on 3 non-contiguous columns in another
worksheet. i
can concatenate the string i'm looking for, but what's the best way to
find that
string in 3 non-contiguous columns in another worksheet.

tried union as the range to search but couldn't get it to work.

find concatenated values question
 

thanks bob, i'll give that a try, too.

--


Gary


"Bob Phillips" wrote in message
...
How about using MATCH

On Error Resume Next
val = ActiveSheet.Evaluate("MATCH(1,(A2:A200=""" & value1 & """)*(C2:C200="""
& value2 & """)*(E2:E200=""" & value3 & """),0)")
If Not IsError(val) Then

'....
End If


--
__________________________________
HTH

Bob

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
yes, a helper column is a possibility, thanks.

--


Gary


"Bob Bridges" wrote in message
...
If I understand you, you have a value "NC123ABCD" and you want to find the
row in another worksheet where "NC" is in column 3, "123" in column 5 and
"ABCD" in column 8. Something like that?

Hmm, I've never tried anything like that. My first thought would be to
create a new column in the other worksheet in which each formula read
=RC3&RC5&RC8 (in the R1C1 notation, obviously) and then do a normal VLOOKUP
or some other kind of Find function using that column instead of trying to
match on the individual columns. Is that possible?

Well, sure it is; even if it's not your workbook and you're not allowed to
modify it, you can have your program open up a read-only copy of it, create
the extra column, use it and then throw it away when you're done; open it
read-only like that and Excel won't allow you to save the changes even
accidentally. How's that?

--- "Gary Keramidas" wrote:
i have to find values based on 3 non-contiguous columns in another
worksheet. i
can concatenate the string i'm looking for, but what's the best way to find
that
string in 3 non-contiguous columns in another worksheet.

tried union as the range to search but couldn't get it to work.