Finding Matching Symbols in 2 Columns

[Manfred]

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.

 

[Biff]

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),INDEX(Q:Q,SMALL(P,ROWS($1:1))),"")

Copy down until you get blanks meaning all the matches have been extracted.

Biff

 

[vernalGreens]

Could you please explain the second formula, that is,
=IF(ROWS($1:1)<=COUNT(P),INDEX(Q:Q,SMALL(P,ROWS($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]

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,ROWS($1:1))),"",INDEX(Q:Q,SMALL(P,ROWS($1:1))))

This error trap:

=IF(ROWS($1:1)<=COUNT(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,ROWS($1:1)) twice:

=IF(ISERROR(SMALL(P,ROWS($1:1))),"",INDEX(Q:Q,SMALL(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

 

[Biff]

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

 

[Manfred]

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 .