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