| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding cells with matching data
COL A has 2,374 cells whose contents look like this:
102050013 102050016 110103030 116300004 161180034 170212025 174301040 174301041 174320025 179221016 190040026 191313016 209241034 210110044 COL B has 4,690 cells whose contents look like this: 102050001102050013 102050011102050016 106211018116040018116300004 110103012110103030 116040018116300004 161180032161180034 170212022170212025 174301015174301034174301040 174301017174301036174301041 174301034174301040 174301036174301041 174320014174320025 179221003179221008179221016 179221008179221016 179221011179221016 190040023190040026 191313002191313016 209241026209241034 210110009210110044 211183002211183027 243020014243020035 The contents of the cells in COL A occur somewhere in the cells in COL C. Two examples: 110103030 is contained in A3 and also in C4. 174320025 is contained in C12 and also in C12. How can I find these matches? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Finding cells with matching data
Gary
In VBA I would do this by looping through the cells (I assume you know how to do this) and comparing the shorter strings to the longer ones using the Instr() function [This function tends to be called "In String"]. If you declared the shorter strings as the variable "strTarget" and the longer ones as "strCheck", the critical bit for each comparison in the loop would be: If Instr(strCheck, strTarget) < 0 When you do an Instring , the value that is returned is the number of the first character at which the match occurs - e.g. if you are comparing "ape" to "caper", the value would be 2 because the string match commences at character number 2. So, in your exercise you are not really concerned with the position at which the match occurs, just whether one occurs at all you're only looking for non-zeroes as the result of your Instr(). The kind of loop that you use and the way you record a match or up to you. -- Time is just the thing that keeps everything from happening all at once "gary" wrote: COL A has 2,374 cells whose contents look like this: 102050013 102050016 110103030 116300004 161180034 170212025 174301040 174301041 174320025 179221016 190040026 191313016 209241034 210110044 COL B has 4,690 cells whose contents look like this: 102050001102050013 102050011102050016 106211018116040018116300004 110103012110103030 116040018116300004 161180032161180034 170212022170212025 174301015174301034174301040 174301017174301036174301041 174301034174301040 174301036174301041 174320014174320025 179221003179221008179221016 179221008179221016 179221011179221016 190040023190040026 191313002191313016 209241026209241034 210110009210110044 211183002211183027 243020014243020035 The contents of the cells in COL A occur somewhere in the cells in COL C. Two examples: 110103030 is contained in A3 and also in C4. 174320025 is contained in C12 and also in C12. How can I find these matches? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Finding cells with matching data
I've never used VBA.
Is there a way to do this in Excel 2007? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Finding cells with matching data
On May 21, 8:20*am, gary wrote:
I've never used VBA. Is there a way to do this in Excel 2007? You can use find function of excel to avoid using vba |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Finding cells with matching data
On May 21, 8:20 am, gary wrote:
I've never used VBA. Is there a way to do this in Excel 2007? On May 20, 8:31 pm, Ayush Jain wrote: You can use find function of excel to avoid using vba On May 20, 8:55 pm, gary wrote: In my example, what is the proper syntax of the find function? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Finding cells with matching data
On May 21, 8:58*am, gary wrote:
On May 21, 8:20 am, gary wrote: I've never used VBA. Is there a way to do this in Excel 2007? On May 20, 8:31 pm, Ayush Jain wrote: You can use find function of excel to avoid using vba On May 20, 8:55 pm, gary wrote: In my example, what is the proper syntax of the find function? Hi Gary, Your 2nd column has a huge number and excel is not supporting more then 15 digit number correctly. Regards, Madiya |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Finding cells with matching data
On May 20, 9:41 pm, Madiya wrote:
On May 21, 8:58 am, gary wrote: On May 21, 8:20 am, gary wrote: I've never used VBA. Is there a way to do this in Excel 2007? On May 20, 8:31 pm, Ayush Jain wrote: You can use find function of excel to avoid using vba On May 20, 8:55 pm, gary wrote: In my example, what is the proper syntax of the find function? Hi Gary, Your 2nd column has a huge number and excel is not supporting more then 15 digit number correctly. Regards, Madiya Then how can I do what I need to do in Excel 2007? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Finding cells with matching data
If I convert the contents of the cells in COL A and COL B from numbers
to strings, would Excel then be able to search the cells in COL B for a string that matches the string in the cells in COL A? What is the format of the formula that would work with my example? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Finding cells with matching data
If I convert the contents of the cells in COL A and COL B from numbers
to strings, would Excel then be able to search the cells in COL B for a string that matches the string in the cells in COL A? What is the syntax of the formula that would work with my example? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Finding cells with matching data
COL A has 2,374 cells containing 9-character strings that look like
this: 102050013 102050016 110103030 116300004 161180034 COL B contains 4,800 cells containing up to 27-characters strings that look like this: 102050001102050013 102050011102050016 106211018116040018116300004 110103012110103030 116040018116300004 161180032161180034 170212022170212025 174301015174301034174301040 174301017174301036174301041 174301034174301040 174301036174301041 174320014174320025 179221003179221008179221016 How do I search for strings in COL B that match strings in COL A? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding a sum by matching two cells with the same identifier | Excel Discussion (Misc queries) | |||
| Finding matching cells | Excel Discussion (Misc queries) | |||
| Finding matching cells | Excel Discussion (Misc queries) | |||
| finding matching cells in worksheets | Excel Worksheet Functions | |||
| finding matching cells in worksheets | Excel Programming | |||
Linear Mode
