Here's the code modified to look in the other workbook for the part numbers.
It really will work 'smoothest' if you have the other workbook open before
you open the one to fill in the data with. Actually, it would probably be
best if the whole thing were turned into a regular Sub type macro rather than
a Function. But one step at a time:
Function GetAllPartNumbers(sourceCell As Range)
'change these 3 Const values to point to the workbook
'and worksheet in that workbook
'and column where the individual part numbers reside
Const plWkBookName = "TCTO_applicability_Mar18_2010_clean.xlsx"
Const plSheetName = "Sheet1" ' change as needed
Const plColumnID = "C"
Dim rawText As String
Dim currentPartID As String
Dim foundParts As String
Dim IncludeDashes As Boolean
Dim partsWB As Workbook
Dim partsSheet As Worksheet
Dim partsList As Range
Dim anyPartEntry As Range
Application.Volatile
'test if the book with the part numbers is open and available
GetAllPartNumbers = ""
On Error Resume Next
Set partsWB = Workbooks(plWkBookName)
If Err < 0 Then
Err.Clear
GetAllPartNumbers = plWkBookName & " Not Open"
Exit Function
End If
'this should set up things to get the part #s from
'the appropriate sheet in TCTO_applicability_Mar18_2010_clean.xlsx
'in column C beginning at row 2.
Set partsSheet = partsWB.Worksheets(plSheetName)
Set partsList = partsSheet.Range(plColumnID & "2:" & _
partsSheet.Range(plColumnID & Rows.Count).End(xlUp).Address)
rawText = sourceCell.Value
If Right(rawText, 1) < "," Then
rawText = rawText & ","
End If
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
Set partsWB = Nothing
End Function
"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?