| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup using concatenated values - Excel 2000 | Excel Worksheet Functions | |||
| find values in multiple cells and paste row values | Excel Discussion (Misc queries) | |||
| find largest values, then return corresponding row values. | Excel Discussion (Misc queries) | |||
| Create Concatenated List with Incremental Values | Excel Programming | |||
| Search/Filter to find values in another range based on two cell values | Excel Programming | |||
Hybrid Mode
