|
Matching data (EXCEL 2007)
Column A has a width of 9 and Column B has a width of 27.
How can I find cells in COL B that contain the same characters (anywhere in the cell) as the cells in COL A? Here's an example of my spreadsheet: COL A 140370005 140370006 140373002 140373014 140373015 COL B 136120008140030020140370004 136120008140030020140370006 136120008140030020140370008 140030019140030020140370004 140030019140030020140370006 140030019140030020140370008 140030020140370004 140030020140370004140370009 140030020140370006 140030020140370006140370010 140030020140370008 140030020140370008140370011 140040007140030020140370004 140040007140030020140370006 140040007140030020140370008 140040011140030020140370004 140040011140030020140370006 140040011140030020140370008 140050018140050026140373002 140050018140050026140373007 140050023140030020140370008 140050024140030020140370004 140050024140030020140370006 140050024140030020140370008 140050025140050026140373002 140050025140050026140373007 140050025140050026140373009 140050025140050026140373011 140050025140050026140373013 140050025140050026140373015 140050026140373002 140050026140373002140373021 140050026140373007 140050026140373007140373016 140050026140373009 140050026140373009140373017 140050026140373011 140050026140373011140373018 140050026140373013 140050026140373013140373019 140050026140373015 140050026140373015140373020 140070013140070018140370001 140070013140070018140370002 140070014140070018140370007 140070014140070018140373012 140070014140070018140373014 140070015140372002 140070015140372002140372007 140070015140372004 140070015140372004140372008 140070015140372005 140070016140050026140373002 140070016140050026140373007 140070016140050026140373015 140070018140370001 140070018140370005 140070018140370005140370010 140070018140370007 140070018140373012 140070018140373012140373019 140070018140373014 140070018140373014140373020 140370003140370009 140373013140373019 140373014140373020 140373015140373020 144030016144030019144314037 144030019144314037 |
Matching data (EXCEL 2007)
this appeared to work okay - adjust range references as needed
=SUMPRODUCT(--(ISNUMBER(MATCH("*"&A$1:A$5&"*",B1,0))))0 entered in cell C1, then copied down. then you can filter column C for TRUE values and copy/paste the results from column B to another sheet. "GARY" wrote: Column A has a width of 9 and Column B has a width of 27. How can I find cells in COL B that contain the same characters (anywhere in the cell) as the cells in COL A? Here's an example of my spreadsheet: COL A 140370005 140370006 140373002 140373014 140373015 COL B 136120008140030020140370004 136120008140030020140370006 136120008140030020140370008 140030019140030020140370004 140030019140030020140370006 140030019140030020140370008 140030020140370004 140030020140370004140370009 140030020140370006 140030020140370006140370010 140030020140370008 140030020140370008140370011 140040007140030020140370004 140040007140030020140370006 140040007140030020140370008 140040011140030020140370004 140040011140030020140370006 140040011140030020140370008 140050018140050026140373002 140050018140050026140373007 140050023140030020140370008 140050024140030020140370004 140050024140030020140370006 140050024140030020140370008 140050025140050026140373002 140050025140050026140373007 140050025140050026140373009 140050025140050026140373011 140050025140050026140373013 140050025140050026140373015 140050026140373002 140050026140373002140373021 140050026140373007 140050026140373007140373016 140050026140373009 140050026140373009140373017 140050026140373011 140050026140373011140373018 140050026140373013 140050026140373013140373019 140050026140373015 140050026140373015140373020 140070013140070018140370001 140070013140070018140370002 140070014140070018140370007 140070014140070018140373012 140070014140070018140373014 140070015140372002 140070015140372002140372007 140070015140372004 140070015140372004140372008 140070015140372005 140070016140050026140373002 140070016140050026140373007 140070016140050026140373015 140070018140370001 140070018140370005 140070018140370005140370010 140070018140370007 140070018140373012 140070018140373012140373019 140070018140373014 140070018140373014140373020 140370003140370009 140373013140373019 140373014140373020 140373015140373020 144030016144030019144314037 144030019144314037 |
Matching data (EXCEL 2007)
You didn't give a lot of details in how you wanted the data listed. So the
easies way is to use the FIND in the Edit Menu. Make sure in the Options that match entire cell is not checked. Press Find All to find all the data. "GARY" wrote: Column A has a width of 9 and Column B has a width of 27. How can I find cells in COL B that contain the same characters (anywhere in the cell) as the cells in COL A? Here's an example of my spreadsheet: COL A 140370005 140370006 140373002 140373014 140373015 COL B 136120008140030020140370004 136120008140030020140370006 136120008140030020140370008 140030019140030020140370004 140030019140030020140370006 140030019140030020140370008 140030020140370004 140030020140370004140370009 140030020140370006 140030020140370006140370010 140030020140370008 140030020140370008140370011 140040007140030020140370004 140040007140030020140370006 140040007140030020140370008 140040011140030020140370004 140040011140030020140370006 140040011140030020140370008 140050018140050026140373002 140050018140050026140373007 140050023140030020140370008 140050024140030020140370004 140050024140030020140370006 140050024140030020140370008 140050025140050026140373002 140050025140050026140373007 140050025140050026140373009 140050025140050026140373011 140050025140050026140373013 140050025140050026140373015 140050026140373002 140050026140373002140373021 140050026140373007 140050026140373007140373016 140050026140373009 140050026140373009140373017 140050026140373011 140050026140373011140373018 140050026140373013 140050026140373013140373019 140050026140373015 140050026140373015140373020 140070013140070018140370001 140070013140070018140370002 140070014140070018140370007 140070014140070018140373012 140070014140070018140373014 140070015140372002 140070015140372002140372007 140070015140372004 140070015140372004140372008 140070015140372005 140070016140050026140373002 140070016140050026140373007 140070016140050026140373015 140070018140370001 140070018140370005 140070018140370005140370010 140070018140370007 140070018140373012 140070018140373012140373019 140070018140373014 140070018140373014140373020 140370003140370009 140373013140373019 140373014140373020 140373015140373020 144030016144030019144314037 144030019144314037 |
Matching data (EXCEL 2007)
In my entire spreadsheet, Col A contains 4,000 cells and COL B
contains 1.8 million cells. Using "FIND" isn't the best method.. |
Matching data (EXCEL 2007)
Since Excel 2007 only has 1,048,576 rows I think 1.8 million is incorrect.
GARY wrote: In my entire spreadsheet, Col A contains 4,000 cells and COL B contains 1.8 million cells. Using "FIND" isn't the best method.. |
Matching data (EXCEL 2007)
You're correct: My list has 1.8 rows so that that's 2 spreadsheets.
|
| All times are GMT +1. The time now is 02:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com