OK here it is.
The list of part numbers (1 in each row) are in column A in file
All_F100_PN.xlsm, while the ones with "(All Dash no.)" or "(all dash
numbers)" are in column C in file TCTO_applicaibility_Mar18_2010_clean.xlsm
I did not tell you that I need to look for numbers with either value after it.
"(All Dash no.)" or "(all dash numbers)" are used and both are acceptable
when looking for candidates for the search in part numbers.
Can the IF statements in your code be updated to show that?
I appreciate the work you have done on this.

"JLatham" wrote:

There's always a catch ... <g
Ok, both workbooks will need to be open to get the job done. And I need to
know the name of the worksheet with the list of part numbers (the real ones)
on it. I think that's in the TCTO_applicability_Mar18_2010_clean.xlsx
workbook, if I understand things correctly.
Give me a little time and I'll modify the code to deal with the 2nd workbook
and post back with modified solution.



"Richard the Lion-Hearted" wrote:

Actually, the list of parts in the one column start at A2 in file
All_F100_PN.xlsx while the "Part Number" column starts in C2 in file
TCTO_applicability_Mar18_2010_clean.xlsx

"JLatham" wrote:

How about a User Defined Function (UDF) solution?

This will do as you want, but of course will need to be tailored to your
workbook. Right now it assumes the sample rows data you show is in column K
on the same sheet. That probably will have to be changed.

But once you get that straight, then you can simply enter the function name
like any other worksheet function into a cell and get the results.

Assuming your 4047122(All Dash no.), 4057222(All Dash no.), 4058222(All Dash
no.), 4060122(All Dash no.) entry is in cell A1, then you'd use the function
like:
=GetAllPartNumbers(A1)
in a cell and the results will be shown.

To put the code into the workbook: open it, press [Alt]+[F11] to open the
VB Editor and then choose Insert -- Module and copy and paste the code below
into it. Make the edits required to identify the sheet and column the parts
numbers list is on/is in and give it a try.

Function GetAllPartNumbers(sourceCell As Range)
'change these 2 Const values to point to the sheet
'and column where the individual part numbers reside
Const plSheetName = "Sheet1"
Const plColumnID = "K"

Dim rawText As String
Dim currentPartID As String
Dim foundParts As String
Dim IncludeDashes As Boolean

Dim partsSheet As Worksheet
Dim partsList As Range
Dim anyPartEntry As Range

GetAllPartNumbers = ""
rawText = sourceCell.Value
If Right(rawText, 1) < "," Then
rawText = rawText & ","
End If

Set partsSheet = ThisWorkbook.Worksheets(plSheetName)
Set partsList = partsSheet.Range(plColumnID & "1:" & _
partsSheet.Range(plColumnID & Rows.Count).End(xlUp).Address)

foundParts = ""
Do While Len(rawText) 1
currentPartID = Left(rawText, _
InStr(rawText, ","))
'remove from raw data
rawText = Right(rawText, _
Len(rawText) - Len(currentPartID))
currentPartID = Left(currentPartID, _
Len(currentPartID) - 1)
IncludeDashes = False
If InStr(currentPartID, "(All Dash no.)") 0 Then
IncludeDashes = True
currentPartID = Trim(Left(currentPartID, _
InStr(currentPartID, "(All Dash no.)") - 1))
End If
currentPartID = Trim(currentPartID)
For Each anyPartEntry In partsList
If IncludeDashes Then
If InStr(anyPartEntry, currentPartID) 0 Then
foundParts = foundParts & anyPartEntry & ","
End If
Else
If Trim(anyPartEntry) = currentPartID Then
foundParts = foundParts & anyPartEntry & ","
End If
End If
Next
Loop
If Len(foundParts) 0 Then
foundParts = Left(foundParts, _
Len(foundParts) - 1)
End If
GetAllPartNumbers = foundParts
Set partsList = Nothing
Set partsSheet = Nothing
End Function



"Richard the Lion-Hearted" wrote:

I have a table called "220_reference" with a column name "Part Number" having
a sample value of below:
4047122(All Dash no.), 4057222(All Dash no.), 4058222(All Dash no.),
4060122(All Dash no.)
The entire value is in one cell representing the "Part Number"
column(defined as general type so text I suppose).
Simple enough. But what I need to do is take any number that has "(All Dash
no.)" after it and search through a column in another table to retrieve any
rows that have that number(text) in it. The other table name is "220" with
one column named pn1_part_no_oem.
A sample of rows (also defined as general type in excel) in the 2nd table is
as follows:
4047122-13
4058222
4058222-705
4057222
4057222-2
4058222-704
4057222-1
4057222-7
4058222-701
4047122
4047122-12
Once retrieved(found), I need to format the output with commas between.
OK, not done though. If each number in the 1st table has "(All Dash no.)"
after it, then I need to provide all numbers from each in the same new cell
with commas between all of them. So, if all 4 numbers above have 4 matches
each, the new cell will have 16 values with commas between. This means that
each number will need to be checked one-at-a-time for the value "(All Dash
no.)" after it, and then process the query for similar values in the 2nd
table's pn1_part_no_oem column.
Given the sample data above, then the new cell's contents would be:
4058222,4058222-701,4058222-704,4058222-705,4057222,4057222-2,4057222-1,4057222-7,4047122,4047122-12,4047122-13
Can anybody help me on this impossible mission?