Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Matching Symbols in 2 Columns
I have a list of stock symbols (filtered using a separate program) in column
Q, along with another list in column R that MAY OR MAY NOT have some of the same symbols. My objective is to place the symbols that match (from columns Q and R) in a separate column (column S). Is it possible for Excel to perform this function, and if so, can someone offer the formula for doing so? Any help would be appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Matching Symbols in 2 Columns
Hi!
If you want to use a helper column: (as I recall, you had about 7000 symbols) Assume the helper column is column P. Assume the symbols are in Q1:Rn. Enter this formula in P1: =IF(COUNTIF(R:R,Q1),ROW(),"") Copy down to Qn. Extract the matches: Enter this formula in some cell, say, T1: =IF(ROWS($1:1)<=COUNT(P:P),INDEX(Q:Q,SMALL(P:P,ROW S($1:1))),"") Copy down until you get blanks meaning all the matches have been extracted. Biff "Manfred" wrote in message ... I have a list of stock symbols (filtered using a separate program) in column Q, along with another list in column R that MAY OR MAY NOT have some of the same symbols. My objective is to place the symbols that match (from columns Q and R) in a separate column (column S). Is it possible for Excel to perform this function, and if so, can someone offer the formula for doing so? Any help would be appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Matching Symbols in 2 Columns
Could you please explain the second formula, that is,
=IF(ROWS($1:1)<=COUNT(P:P),INDEX(Q:Q,SMALL(P:P,ROW S($1:1))),"") Specifically, 1) How is Rows($1:1) evaluated? 2) What is the significance of rows<=count? 3) Index takes a range, row num, column num as parameters. why is column num not specified? Biff wrote: Hi! If you want to use a helper column: (as I recall, you had about 7000 symbols) Assume the helper column is column P. Assume the symbols are in Q1:Rn. Enter this formula in P1: =IF(COUNTIF(R:R,Q1),ROW(),"") Copy down to Qn. Extract the matches: Enter this formula in some cell, say, T1: =IF(ROWS($1:1)<=COUNT(P:P),INDEX(Q:Q,SMALL(P:P,ROW S($1:1))),"") Copy down until you get blanks meaning all the matches have been extracted. Biff "Manfred" wrote in message ... I have a list of stock symbols (filtered using a separate program) in column Q, along with another list in column R that MAY OR MAY NOT have some of the same symbols. My objective is to place the symbols that match (from columns Q and R) in a separate column (column S). Is it possible for Excel to perform this function, and if so, can someone offer the formula for doing so? Any help would be appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Matching Symbols in 2 Columns
Hi!
1) How is Rows($1:1) evaluated? ROWS() returns the number of rows in the referenced range or array. The range arguments can be entire rows like 1:10 or cell references like A1:A10 or array constants like {1,2,3,4,5,6,7,8,9,10}. All of those examples evaluate exactly the same. The result would be 10. When you use cell references like A1, The column reference A is ignored. 2) What is the significance of rows<=count? That is being used as a pseudo error trap. A "typical" error trap might look like this: (some might even include the INDEX in ISERROR which is not necessary since that is not where an error will be generated) =IF(ISERROR(SMALL(P:P,ROWS($1:1))),"",INDEX(Q:Q,SM ALL(P:P,ROWS($1:1)))) This error trap: =IF(ROWS($1:1)<=COUNT(P:P) Is slightly shorter and is overall, more efficient. There will only be a certain number of matches to be returned so that means the formula will need to be copied to a certain number of cells. That string of formula compares the number of matches to the number of cells that the formula is copied to. If the number of cells being copied to is less than or equal to the number of matches, the formula returns the appropriate match, otherwise, returns a formula blank: "". When the error trap in the below formula evaluates to FALSE then the formula has to process this expression: SMALL(P:P,ROWS($1:1)) twice: =IF(ISERROR(SMALL(P:P,ROWS($1:1))),"",INDEX(Q:Q,SM ALL(P:P,ROWS($1:1)))) 3) Index takes a range, row num, column num as parameters. why is column num not specified? Because we're only indexing a single column. If the column_num argument is ommitted, it defaults to 1. Biff wrote in message ups.com... Could you please explain the second formula, that is, =IF(ROWS($1:1)<=COUNT(P:P),INDEX(Q:Q,SMALL(P:P,ROW S($1:1))),"") Specifically, 1) How is Rows($1:1) evaluated? 2) What is the significance of rows<=count? 3) Index takes a range, row num, column num as parameters. why is column num not specified? Biff wrote: Hi! If you want to use a helper column: (as I recall, you had about 7000 symbols) Assume the helper column is column P. Assume the symbols are in Q1:Rn. Enter this formula in P1: =IF(COUNTIF(R:R,Q1),ROW(),"") Copy down to Qn. Extract the matches: Enter this formula in some cell, say, T1: =IF(ROWS($1:1)<=COUNT(P:P),INDEX(Q:Q,SMALL(P:P,ROW S($1:1))),"") Copy down until you get blanks meaning all the matches have been extracted. Biff "Manfred" wrote in message ... I have a list of stock symbols (filtered using a separate program) in column Q, along with another list in column R that MAY OR MAY NOT have some of the same symbols. My objective is to place the symbols that match (from columns Q and R) in a separate column (column S). Is it possible for Excel to perform this function, and if so, can someone offer the formula for doing so? Any help would be appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Matching Symbols in 2 Columns
Clarification:
some might even include the INDEX in ISERROR which is not necessary since that is not where an error will be generated Well, it is possible if the indexed range contains formula errors already. But this is based on knowing that errors will be generated and are therefore expected versus unexpected errors. Biff "Biff" wrote in message ... Hi! 1) How is Rows($1:1) evaluated? ROWS() returns the number of rows in the referenced range or array. The range arguments can be entire rows like 1:10 or cell references like A1:A10 or array constants like {1,2,3,4,5,6,7,8,9,10}. All of those examples evaluate exactly the same. The result would be 10. When you use cell references like A1, The column reference A is ignored. 2) What is the significance of rows<=count? That is being used as a pseudo error trap. A "typical" error trap might look like this: (some might even include the INDEX in ISERROR which is not necessary since that is not where an error will be generated) =IF(ISERROR(SMALL(P:P,ROWS($1:1))),"",INDEX(Q:Q,SM ALL(P:P,ROWS($1:1)))) This error trap: =IF(ROWS($1:1)<=COUNT(P:P) Is slightly shorter and is overall, more efficient. There will only be a certain number of matches to be returned so that means the formula will need to be copied to a certain number of cells. That string of formula compares the number of matches to the number of cells that the formula is copied to. If the number of cells being copied to is less than or equal to the number of matches, the formula returns the appropriate match, otherwise, returns a formula blank: "". When the error trap in the below formula evaluates to FALSE then the formula has to process this expression: SMALL(P:P,ROWS($1:1)) twice: =IF(ISERROR(SMALL(P:P,ROWS($1:1))),"",INDEX(Q:Q,SM ALL(P:P,ROWS($1:1)))) 3) Index takes a range, row num, column num as parameters. why is column num not specified? Because we're only indexing a single column. If the column_num argument is ommitted, it defaults to 1. Biff wrote in message ups.com... Could you please explain the second formula, that is, =IF(ROWS($1:1)<=COUNT(P:P),INDEX(Q:Q,SMALL(P:P,ROW S($1:1))),"") Specifically, 1) How is Rows($1:1) evaluated? 2) What is the significance of rows<=count? 3) Index takes a range, row num, column num as parameters. why is column num not specified? Biff wrote: Hi! If you want to use a helper column: (as I recall, you had about 7000 symbols) Assume the helper column is column P. Assume the symbols are in Q1:Rn. Enter this formula in P1: =IF(COUNTIF(R:R,Q1),ROW(),"") Copy down to Qn. Extract the matches: Enter this formula in some cell, say, T1: =IF(ROWS($1:1)<=COUNT(P:P),INDEX(Q:Q,SMALL(P:P,ROW S($1:1))),"") Copy down until you get blanks meaning all the matches have been extracted. Biff "Manfred" wrote in message ... I have a list of stock symbols (filtered using a separate program) in column Q, along with another list in column R that MAY OR MAY NOT have some of the same symbols. My objective is to place the symbols that match (from columns Q and R) in a separate column (column S). Is it possible for Excel to perform this function, and if so, can someone offer the formula for doing so? Any help would be appreciated. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Matching Symbols in 2 Columns
Actually, this is a different problem than the previous one (that you
generously resolved, incidentally). The previous issue concerned ranking two columns containing the SAME symbols, whereas the above issue concerns the matching of two columns which MAY OR MAY NOT contain the same symbols. Thank you for your assistance and follow-up clarification . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the Latest Date from Several Dates in Different Columns | Excel Discussion (Misc queries) | |||
Finding One Value, Matching Three Criteria | Excel Discussion (Misc queries) | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
Hidden Columns in Shared Workbooks | Excel Discussion (Misc queries) | |||
Matching data in multiple columns | Excel Discussion (Misc queries) |