| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching 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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Matching cells (Excel 2007)
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" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Matching cells (Excel 2007)
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 ============================= On May 20, 12:54 am, "Bob Phillips" wrote: 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" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Matching cells (Excel 2007)
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) "gary" wrote in message ... 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 ============================= On May 20, 12:54 am, "Bob Phillips" wrote: 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" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matching data (EXCEL 2007) | Excel Discussion (Misc queries) | |||
| Matching and returning values to a master sheet in Excel 2007 | Excel Worksheet Functions | |||
| EXCEL 2007 MATCHING ANSWERS | Excel Discussion (Misc queries) | |||
| Matching records (Excel 2007) | Excel Programming | |||
| Data Matching Button for MS Excel 2007 or later | Excel Worksheet Functions | |||
Linear Mode
