Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace Old Part Numbers with New Part Numbers in a Macro. | Excel Discussion (Misc queries) | |||
Why are 1/2 my numbers imported as text and the rest as numbers? | Excel Discussion (Misc queries) | |||
Vlookup referencing one column that contains part numbers in both. | Excel Worksheet Functions | |||
Counting Unique Part Numbers In A Range | Excel Worksheet Functions | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) |