He has a list with data in 4 columns where column A is very high-level and column D is more specific. (It's a business taxonomy but my example is food-related) For example, column A might say Bakery, Column B would contain Bread and Cake, Column C would say Sliced and Not Sliced for the Bread and Chocolate and Vanilla for the Cake. Colum D would then list all the kids of breads like white, wheat, etc...
He needs to transform it (via code, so it is repeatable) so that on a new sheet in column A it shows the unique items from Colum A and then it puts those same values across the top and then continues to drill down in that manner.
I know, it's odd and complicated.
I've attached a color-coded example for reference.
I can do the unique values from Column A with this code:
Code: Select all
Sub A_Unique_B()
Dim X
Dim objDict As Object
Dim lngRow As Long
Set objDict = CreateObject("Scripting.Dictionary")
X = Application.Transpose(Range([a1], Cells(Rows.Count, "A").End(xlUp)))
For lngRow = 1 To UBound(X, 1)
objDict(X(lngRow)) = 1
Next
Range("B1:B" & objDict.Count) = Application.Transpose(objDict.keys)
End Sub
And there can be number of L2 values for an L1, any number of L3s for an L2, and any number of L4s for an L3. Including zero.
Any pointers?
Thanks!