Matching cells (Excel 2007)

[gary]

The cells in COL A look like this:

140370005
140370006
140373002
140373014
140373015

COL A has 2,374 cells

==========================

The cells in COL B look like this:

140370005
140370006136120008140030020140370004
136120008140030020140370006
136120008140030020140370008
140030019140030020140370004
140030019140030020140370006
140030019140030020140370008
140030020140370004
140030020140370004140370009
14003002014136120008140030020140370004
136120008140030020140370006
136120008140030020140370008
140030019140030020140370004
140030019140030020140370006
140030019140030020140370008
140030020140370004
140030020140370004140370009
140030020140370006
140030020140370006140370010
140030020140370008
140030020140370008140370011
140040007140030020140370004
14004000714
140373015

COL B has 1,050,000 cells.

==============================

I need to find the cells in COL B that contain (anywhere in the cell)
the same sequence of characters as the cells in COL A.

==============================

It was suggested to paste this formula:

=SUMPRODUCT(--(ISNUMBER(MATCH("*"&A$1:A$2374&"*",B1,0))))>0

in C1 and then "copy the formula down".

Do I copy the formula from C1 thru C2374 or from C1 to C1050000?

 

[Bob Phillips]

Just use

=SUMPRODUCT(--(A$1:A$2374<>""),--(ISNUMBER(FIND(A$1:A$2374,B1))))>0

and copy down to C5000

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[gary]

Hi Bob,

I thought I would need to copy the formula eiher:

from C1 to C2374 (the number of cells in COL A)
or from C1 to C1050000 (the number of cells in COL B).

But "down to C5000" doesn't relate to anything. Why C5000?


gARY

=============================

 

[Bob Phillips]

Sorry, me mis-reading. I meant down to C1050000

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)