Finding cells with matching data

G

gary

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?
 
N

Neptune Dinosaur

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.
 
G

gary

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

On May 20, 8:55 pm, gary wrote:
In my example, what is the proper syntax of the find function?
 
M

Madiya

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

On May 20, 8:55 pm, gary wrote:
In my example, what is the proper syntax of the find function?
Click to expand...

Hi Gary,
Your 2nd column has a huge number and excel is not supporting more
then 15 digit number correctly.
Regards,
Madiya
 
G

gary

Hi Gary,
Your 2nd column has a huge number and excel is not supporting more
then 15 digit number correctly.
Regards,
Madiya
Click to expand...

Then how can I do what I need to do in Excel 2007?
 
G

gary

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?
 
G

gary

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?
 
G

gary

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?
 
Top