Log In
Register
Home
July 17, 2019
Offline
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
October 5, 2010
Offline
Hi Bill,
No file attached.
regards
Phil
July 17, 2019
Offline
Please check.
Thanks;
Bill
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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
1 Guest(s)
