Identifying Part Numbers
 

Column A of my spreadsheet has several hundred "parent" part numbers.
Column C has several thousand "child" part numbers.

A parent part number is made up of a combination of characters/digits: ex.
TMCU 04-06. The length and combination of characters/digits varies.

A child part number is supposed to be made up with the parent's part number
and other characters/digits: ex. SUS-TMCU 04-06-NBR, or TMCU 04-06-D99, or
SDE-TMCU 04-06, etc. Unfortunately, these other characters/digits can be
placed before, after, or on both sides of the parent part number and vary in
length...

What I would like to do is identify all those child part numbers that have a
corresponding parent and also those child part numbers that have been
(potentially) coded wrong. Therefore, listed next to each child part
number, I would like to have (column D) the cell address of the parent and
the actual parent part number (column E). If no perfect match exists, then
there shall be (column F) the cell address of the part number in column A
that comes the closest (in terms of sequential characters/digits) to the
part number in column C, the actual part number of that cell address (column
G), and the maximum number of sequential characters/digits matched (column
H).

Thanks in advance to whoever can help me out.
--
Tiziano

Identifying Part Numbers
 

Some thoughts to get you going on this 1st part of your quest ..
.. Therefore, listed next to each child part number,
I would like to have (column D) the cell address of the parent
and the actual parent part number (column E).

Assumptions:
Parent part numbers are housed within A2:A200
Child part numbers are listed in C2 down

In D2, array-entered (press CRTL+SHIFT+ENTER):
="A"&MATCH(1,ISNUMBER(SEARCH($A$2:$A$200,C2))*($A$ 2:$A$200<""),0)+1

In E2, array-entered (press CRTL+SHIFT+ENTER):
=INDEX($A$2:$A$200,MATCH(1,ISNUMBER(SEARCH($A$2:$A $200,C2))*($A$2:$A$200<""),0))

Select D2:E2, copy down to the last row of data in col C
Note: Adapt the range: $A$2:$A$200 to suit before copying down

Col D will return the cell address of the parent part number,
while col E returns the actual parent part number ..

Note: Replace SEARCH with FIND in the 2 formulas if you need a stricter
case-sensitive search. SEARCH is not case sensitive.

As for the 2nd part of your quest, I've got no ideas to offer you. But you
could proceed to auto-filter out the lines with #N/A returns in either col D
or E for closer inspections
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tiziano" wrote:
Column A of my spreadsheet has several hundred "parent" part numbers.
Column C has several thousand "child" part numbers.

A parent part number is made up of a combination of characters/digits: ex.
TMCU 04-06. The length and combination of characters/digits varies.

A child part number is supposed to be made up with the parent's part number
and other characters/digits: ex. SUS-TMCU 04-06-NBR, or TMCU 04-06-D99, or
SDE-TMCU 04-06, etc. Unfortunately, these other characters/digits can be
placed before, after, or on both sides of the parent part number and vary in
length...

What I would like to do is identify all those child part numbers that have a
corresponding parent and also those child part numbers that have been
(potentially) coded wrong. Therefore, listed next to each child part
number, I would like to have (column D) the cell address of the parent and
the actual parent part number (column E). If no perfect match exists, then
there shall be (column F) the cell address of the part number in column A
that comes the closest (in terms of sequential characters/digits) to the
part number in column C, the actual part number of that cell address (column
G), and the maximum number of sequential characters/digits matched (column
H).

Thanks in advance to whoever can help me out.
--
Tiziano

Identifying Part Numbers
 

Your functions work great, Max!
Thanks for your help.
Does anyone have any suggestions for the second part of my problem?
--
Tiziano

"Max" wrote in message
...
Some thoughts to get you going on this 1st part of your quest ..
.. Therefore, listed next to each child part number,
I would like to have (column D) the cell address of the parent
and the actual parent part number (column E).

Assumptions:
Parent part numbers are housed within A2:A200
Child part numbers are listed in C2 down

In D2, array-entered (press CRTL+SHIFT+ENTER):
="A"&MATCH(1,ISNUMBER(SEARCH($A$2:$A$200,C2))*($A$ 2:$A$200<""),0)+1

In E2, array-entered (press CRTL+SHIFT+ENTER):
=INDEX($A$2:$A$200,MATCH(1,ISNUMBER(SEARCH($A$2:$A $200,C2))*($A$2:$A$200<""),0))

Select D2:E2, copy down to the last row of data in col C
Note: Adapt the range: $A$2:$A$200 to suit before copying down

Col D will return the cell address of the parent part number,
while col E returns the actual parent part number ..

Note: Replace SEARCH with FIND in the 2 formulas if you need a stricter
case-sensitive search. SEARCH is not case sensitive.

As for the 2nd part of your quest, I've got no ideas to offer you. But you
could proceed to auto-filter out the lines with #N/A returns in either col
D
or E for closer inspections
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tiziano" wrote:
Column A of my spreadsheet has several hundred "parent" part numbers.
Column C has several thousand "child" part numbers.

A parent part number is made up of a combination of characters/digits:
ex.
TMCU 04-06. The length and combination of characters/digits varies.

A child part number is supposed to be made up with the parent's part
number
and other characters/digits: ex. SUS-TMCU 04-06-NBR, or TMCU 04-06-D99,
or
SDE-TMCU 04-06, etc. Unfortunately, these other characters/digits can be
placed before, after, or on both sides of the parent part number and vary
in
length...

What I would like to do is identify all those child part numbers that
have a
corresponding parent and also those child part numbers that have been
(potentially) coded wrong. Therefore, listed next to each child part
number, I would like to have (column D) the cell address of the parent
and
the actual parent part number (column E). If no perfect match exists,
then
there shall be (column F) the cell address of the part number in column A
that comes the closest (in terms of sequential characters/digits) to the
part number in column C, the actual part number of that cell address
(column
G), and the maximum number of sequential characters/digits matched
(column
H).

Thanks in advance to whoever can help me out.
--
Tiziano

Identifying Part Numbers
 

Tiziano, you're welcome !
Good luck with the 2nd part of your prob ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tiziano" wrote in message
...
Your functions work great, Max!
Thanks for your help.
Does anyone have any suggestions for the second part of my problem?
--
Tiziano