If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
cell to have comma-delimited values based on text
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? |
#2
|
|||
|
|||
cell to have comma-delimited values based on text
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? |
#3
|
|||
|
|||
cell to have comma-delimited values based on text
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? |
#4
|
|||
|
|||
cell to have comma-delimited values based on text
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? |
#5
|
|||
|
|||
cell to have comma-delimited values based on text
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? |
#6
|
|||
|
|||
cell to have comma-delimited values based on text
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? |
#7
|
|||
|
|||
cell to have comma-delimited values based on text
OK, I elected to go with the Sub due primarily to the need to have that 2nd
workbook open and problems that can arise if it isn't and we're using a Function. This does take the 2 phrases for including dash numbers into account. You still need to provide the specific sheet name in the workbooks that the lists are on; there's places in the code to do that, so it should adapt to your workbooks well. I'd test on copies of them first, just in case. Sub GetAllPartNumbers() 'these Const values are for the sheet 'and column in THIS WORKBOOK where the '"4047122(All Dash No.)" type entries reside Const ckListSheetName = "Sheet1" ' change as needed Const ckListColumnID = "A" 'change these 2 Const values to point to the sheet '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" 'phrases to search for to determine if all dash numbers 'are to be included. ENTER IN ALL CAPS Const phrase1 = "(ALL DASH NO.)" Const phrase2 = "(ALL DASH NUMBERS)" 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 ' Dim ckListWS As Worksheet Dim ckListRange As Range Dim anyCkListEntry As Range 'test if the book with the part numbers is open and available On Error Resume Next Set partsWB = Workbooks(plWkBookName) If Err 0 Then Err.Clear MsgBox "Required Workbook" & vbCrLf & _ plWkBookName & vbCrLf & _ " Is Not Open", vbOKOnly, "Cannot Continue..." Exit Sub End If On Error GoTo 0 '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) 'now set up reference to the worksheet/column in this workbook Set ckListWS = ThisWorkbook.Worksheets(ckListSheetName) Set ckListRange = ckListWS.Range(ckListColumnID & "2:" & _ ckListWS.Range(ckListColumnID & Rows.Count).End(xlUp).Address) 'work through this workbook's entries For Each anyCkListEntry In ckListRange If Not IsEmpty(anyCkListEntry) Then rawText = UCase(anyCkListEntry.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 'have to test separately rather than 'with an OR because of need to remove 'the specific indicator If InStr(currentPartID, phrase1) 0 Then IncludeDashes = True currentPartID = Trim(Left(currentPartID, _ InStr(currentPartID, phrase1) - 1)) End If If InStr(currentPartID, phrase2) 0 Then IncludeDashes = True currentPartID = Trim(Left(currentPartID, _ InStr(currentPartID, phrase2) - 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 & Trim(Str(anyPartEntry)) & "," End If End If Next Loop If Len(foundParts) 0 Then foundParts = Left(foundParts, _ Len(foundParts) - 1) End If 'put the results on the sheet 'change offset 2nd parameter to "push" to 'other columns. 1 = 1 column to right of the ' "4047122(All Dash No.)" entries column 'the single quote forces numbers to appear as text 'and is needed when no dash numbers are included anyCkListEntry.Offset(0, 1) = "'" & foundParts End If ' end of test for empty cells Next ' end of loop through this workbook's list 'housekeeping Set partsList = Nothing Set partsSheet = Nothing Set partsWB = Nothing Set ckListRange = Nothing Set ckListWS = Nothing 'let the user know it's finished MsgBox "Task Completed" End Sub "Richard the Lion-Hearted" wrote: 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? |
#8
|
|||
|
|||
cell to have comma-delimited values based on text
Thanks a bunch.
I put in a elseif for the second string but it compiled and works great. I find that I have to create a second module and name it different if I need to use a different named worksheet in the same file with the part number list. That is, if I have part numbers in column A in two separate worksheets "sheet1" and "sheet2", the 1st module will only look at "sheet1", while the 2nd looks at "sheet2" as it is defined that way. This is OK as the other file will also have a "sheet1" and "sheet2" which have to reference the same named worksheet. So in file 1 "sheet1" == file 2 "sheet1", etc. It just would be better to automate with one module. Any suggestions? "JLatham" wrote: 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? |
#9
|
|||
|
|||
cell to have comma-delimited values based on text
I've added an extra optional parameter to the call to the function to
indicate which sheet name should be used for the parts list. That's the variable whatSheet which is an integer and expects either a 1 or 2. If not 1, then for the moment 2 is assumed. If not specified at all, then 1 is the default. So =GetAllPartNumbers(A1,1) is the same as =GetAllPartNumbers(A1) and would end up using "Sheet1", but =GetAllPartNumbers(A1,2) will cause it to use "Sheet2" as the sheet with the parts list on it. This version of the function also deals with both possible "All Dash No./Numbers" entries. Her's the revised function Function GetAllPartNumbers(sourceCell As Range, _ Optional whatSheet As Integer = 1) 'INPUTS: sourceCell is cell address with ' list of entries to parse ' whatSheet is an optional integer ' with value of 1 or 2: ' value = 1 (default if not specified) ' uses "Sheet1" for source of parts list ' value = 2 uses "Sheet2" for source of parts list ' 'change these 2 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 plColumnID = "C" Dim plSheetName As String Const phrase1 = _ "(ALL DASH NO.)" ' use ALL CAPS here Const phrase2 = _ "(ALL DASH NUMBERS)" ' use ALL CAPS HERE 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 Select Case whatSheet Case Is = 1 plSheetName = "Sheet1" Case Else ' must be 2 or just wrong! plSheetName = "Sheet2" End Select 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(UCase(currentPartID), phrase1) 0 Then IncludeDashes = True currentPartID = Trim(Left(currentPartID, _ InStr(UCase(currentPartID), phrase1) - 1)) ElseIf InStr(UCase(currentPartID), phrase2) 0 Then IncludeDashes = True currentPartID = Trim(Left(currentPartID, _ InStr(UCase(currentPartID), phrase2) - 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: Thanks a bunch. I put in a elseif for the second string but it compiled and works great. I find that I have to create a second module and name it different if I need to use a different named worksheet in the same file with the part number list. That is, if I have part numbers in column A in two separate worksheets "sheet1" and "sheet2", the 1st module will only look at "sheet1", while the 2nd looks at "sheet2" as it is defined that way. This is OK as the other file will also have a "sheet1" and "sheet2" which have to reference the same named worksheet. So in file 1 "sheet1" == file 2 "sheet1", etc. It just would be better to automate with one module. Any suggestions? "JLatham" wrote: 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? |
Thread Tools | |
Display Modes | |
|
|