Hi,
Please can you help me to distribute the Categories on Product lines as below.
Input:
Sheet1: Column A " Part Number", Column B"Product Line"
Sheet2: Column A " Product Line ", Column B"Category".
Output:
Sheet1: Column C
Please check attached file to get sample.
Thanks;
Bill
Hi Bill,
No file attached.
regards
Phil
Please check.
Thanks;
Bill
Hi Bill,
Here is a code you can try:
Sub GetCategory()
Dim Dict As Object: Set Dict = CreateObject("scripting.dictionary")
Dim i As Long, CategString As String, ItemsList As Variant, Itm As Variant
With ThisWorkbook.Worksheets("Product Line & Category")
i = 2
'load categories to dictionary
Do
Dict(.Cells(i, "A").Value) = .Cells(i, "B").Value
i = i + 1
Loop Until Len(.Cells(i, "A")) = 0
End With
With ThisWorkbook.Worksheets("Parts")
i = 2
Do
CategString = ""
ItemsList = Split(.Cells(i, "B").Value, "|")
For Each Itm In ItemsList
If Dict.Exists(CStr(Itm)) Then
If InStr(1, CategString, Dict(CStr(Itm)), vbTextCompare) = 0 Then CategString = CategString & "|" & Dict(CStr(Itm))
End If
Next Itm
If CategString Like "|*" Then CategString = Right(CategString, Len(CategString) - 1)
.Cells(i, "C").Value = CategString
i = i + 1
Loop Until Len(.Cells(i, "A")) = 0
End With
End Sub
Thank you very much; your support is greatly appreciated.
Thanks;
Bill
