Clarification: Finding/Listing Symbols Found in 2 Columns

[Manfred]

I thought I knew how to perform the following simple function, but I must be
missing something basic.

The problem: I am assembling a spreadsheet consisting of 2 columns
of symbols. The two columns may or may not have matching symbols.

The question: Is there a formula what will search through both columns and
list symbols that appear in BOTH columns, LISTING these symbols in a
separate third column?

Thank you for any response.

 

[T. Valko]

Define symbols. Do you mean stock ticker symbols?

Are both columns the same length? You only need to check the shortest
column. How many rows does the shortest column contain?

Biff

 

[Ron Coderre]

Was there an issue with the formula I posted to your same question on
04-May-2007? That formula lists only items that appear in BOTH lists.

***********
Regards,
Ron

XL2002, WinXP

 

[Manfred]

Biff,

Thanks greatly for responding. Both columns, consisting of stock symbols,
may have as many as 250 rows, but neither is likely to have the same number
of rows, and both will vary with each computation effort.

 

[T. Valko]

Ok, I see that Ron answered your other post. The solution he provided should
work. What kind of problem are you having?

Here's a tweaked version of Ron's formula that adds an error trap and
includes an "enhanced offset mechanism" (which is where a lot of people have
problems with this type of formula). This is still an array** formula. It's
comparing column A to column B:

Data starting in row 2.

=IF(ROWS($1:1)<=COUNT(1/(ISNUMBER(MATCH(A$2:A$10,B$2:B$10,0)))),INDEX(A$2:A$10,SMALL(IF(COUNTIF(B$2:B$10,A$2:A$10),ROW(A$2:A$10)-MIN(ROW(A$2:A$10))+1),ROWS($1:1))),"")

Copy down until you get blanks.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

 

[Manfred]

Biff,

Thank you very much for responding. My apologies, but I can't find the
answer or formula that Ron gave. Sorry if this is a stupid question, but
concerning the formula that you generously provide, are the column/rows to
remain as e.g. A$2:A$10 and B$2:B$10 even if I've got, say, 200 rows in
column A and 40 rows in column B?

Also, are array formulae copied from row to row in the same manner as
non-array formulae?

Thank you for any response.

 

[T. Valko]

No, you need to adjust the ranges to meet your needs.

Remember what I said in my other reply: you only need to check the shortest
column. In the formula I posted it is checking column A against column B. In
that example both columns are equal in length so it doesn't matter which one
you check. You want to check column B (the shortest column) against column A
so we need to "flip" some of the references. In this formula the ranges are
B2:B10 and A2:A20. Still array entered:

=IF(ROWS($1:1)<=COUNT(1/(ISNUMBER(MATCH(B$2:B$10,A$2:A$20,0)))),INDEX(B$2:B$10,SMALL(IF(COUNTIF(A$2:A$20,B$2:B$10),ROW(A$2:A$10)-MIN(ROW(A$2:A$10))+1),ROWS($1:1))),"")

Also, are array formulae copied from row to row in the same manner as
non-array formulae?

Yes. Enter the formula in one cell then "grab" the fill handle and drag down
until you get blanks as results meaning all the matches have been extracted.

If you want something less complicated then it requires the use of an
additional column of formulas.

Assume the ranges are A2:A20 and B2:B10. We want to check the shortest
column so that's column B.

Enter this formula in C2:

=IF(ISNUMBER(MATCH(B2,A$2:A$20,0)),ROW(),"")

Copy down to C10.

Then use this formula to extract the matches.

=IF(ROWS($1:1)<=COUNT(C:C),INDEX(B$2:B$10,MATCH(SMALL(C$2:C$10,ROWS($1:1)),C$2:C$10,0)),"")

Copy down until you get blanks.

Biff