 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2008Matching cells (Excel 2007)
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? Just use =SUMPRODUCT(--(A$1:A$2374<>""),--(ISNUMBER(FIND(A$1:A$2374,B1))))>0 and copy down to C5000  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > The cells in COL A look like this: > [quoted text clipped - 51 lines] > > Do I copy the formula from C1 thru C2374 or from C1 to C1050000? 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 ============================= > Just use > [quoted text clipped - 65 lines] > > > Do I copy the formula from C1 thru C2374 or from C1 to C1050000? Sorry, me mis-reading. I meant down to C1050000 Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi Bob, > [quoted text clipped - 79 lines] >> >> > Do I copy the formula from C1 thru C2374 or from C1 to C1050000? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.