Matching partial portions of cell contents

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I have 3 columns of alphanumeric data. The first represents a list of
partial license numbers with only numeric data. The 2nd and 3rd
represent complete license numbers with alphanumeric data. I want to
find matches for as many digits of the partial license numbers (in the
1st column) as possible with the complete license numbers in the 2nd
and 3rd columns. Is there a way to do this in Excel?
 
If I understand what you're after, I would think that you'd want to separate
the 2 columns containing the complete plate numbers, so that you could
individually mark *each cell* in those 2 columns, where a match was found
with the partial numbers in the first column.

Or am I on the wrong track?

Say you have 50 rows of partials in Column A, from A1 to A50, and 2 columns,
100 rows each, with complete numbers, from B1 to C100.

Insert a new column between B & C, making the second column of complete
numbers the NEW Column D.

In the NEW, blank Column C, enter this in C1:

=SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$50,B1))))

And copy down to C100.

In E1 enter:

=SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$50,D1))))

And copy down to E100.

This should give you the number of hits (matches) for each complete plate
number against the list of partials in Column A.
 
I tried this but I'm not sure I did it right or how to interpret what
came up. You can see the actual file by going to
http://www10.sendthisfile.com/d.jsp?t=tCJpkJBOJM3elTd0lGjdKo9a.

Column A has the incomplete license (they're not license plate
numbers, by the way) numbers, and Columns B and C have complete
numbers. I'm looking to get as close as possible to a match--say at
least 4 digits from the numbers in Col A in the same order in Cols B
or C.
 
Follow my directions exactly as I stated them in my first post.

Change the formula to make up for the actual range of partials to:

=SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$1258,B28))))
And enter in the NEW C1.

Enter this in E1:
=SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$1258,D28))))

You can now *double* click on the fill handle of C1 and E1 respectively,
And the formula will *automatically* be copied down the columns as far as
there is data in the adjoining columns.

A 0 in Columns C and E mean there is no match in B and/or D..

If you get a return of 1 or 2, that means the complete license number has a
number series that is contained (matched) in Column A, in either 1 or 2
instances.
 
Ah, thanks. Now, having identified the fact that there is a match
somewhere in Col A, is there a way to flag, e.g., change font color or
format, those cells where the match has been found?
 
How about returning just the *row number* of the first match?

Try this *array* formula in C1:

=IF(SUM(--(ISNUMBER(FIND($A$1:$A$1258,B1))))=0,"",
MATCH(TRUE,ISNUMBER(FIND($A$1:$A$1258,B1)),0))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.


*AFTER* the CSE entry, copy down to C33.


Use this *array* formula in E1, and after the CSE entry, copy down to E116:

=IF(SUM(--(ISNUMBER(FIND($A$1:$A$1258,D1))))=0,"",
MATCH(TRUE,ISNUMBER(FIND($A$1:$A$1258,D1)),0))

This formula will either return a blank cell if no match is found, or the
*row number* of the first match in Column A.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Ah, thanks. Now, having identified the fact that there is a match
somewhere in Col A, is there a way to flag, e.g., change font color or
format, those cells where the match has been found?
 
How about returning just the *row number* of the first match?

Try this *array* formula in C1:

=IF(SUM(--(ISNUMBER(FIND($A$1:$A$1258,B1))))=0,"",
MATCH(TRUE,ISNUMBER(FIND($A$1:$A$1258,B1)),0))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.  Also, you must use CSE when
revising the formula.

*AFTER* the CSE entry, copy down to C33.

Use this *array* formula in E1, and after the CSE entry, copy down to E116:

=IF(SUM(--(ISNUMBER(FIND($A$1:$A$1258,D1))))=0,"",
MATCH(TRUE,ISNUMBER(FIND($A$1:$A$1258,D1)),0))

This formula will either return a blank cell if no match is found, or the
*row number* of the first match in Column A.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Ah, thanks. Now, having identified the fact that there is a match
somewhere in Col A, is there a way to flag, e.g., change font color or
format, those cells where the match has been found?









- Show quoted text -
Click to expand...

That's perfect! Thanks much!
 
You're welcome, and as far as your feed-back ... WHY did you ruin <bg> a
good relationship by bottom posting that feed-back when all the rest of your
posts were properly top posted?
Just kidding(maybe?).
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

- Show quoted text -
Click to expand...

That's perfect! Thanks much!
 
Just in a hurry, I guess. Mea culpa.

You're welcome, and as far as your feed-back ... WHY did you ruin <bg> a
good relationship by bottom posting that feed-back when all the rest of your
posts were properly top posted?
Just kidding(maybe?).
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



That's perfect! Thanks much!
Click to expand...
 
Back
Top