我尝试使用字典和递归函数来呈现结果。您可以对其进行一些调整以仅显示顶部部分。目前它显示 A 列中的每个项目。C 列是输出。
我的想法是,我循环遍历 A 列,并为每个部分创建一个字典,并在字典中包含子部分的条目。
当我呈现结果时,如果字典中的条目也是我的顶级字典中的条目,我会再次呈现它。
Public Sub sFindParts()
Dim topPartDict As New Dictionary, subPartDict As Dictionary, d As Dictionary
Dim topPartList As Range, part As Range
Dim outputLocation As Range
Dim i As Integer, indLvl As Integer
Dim k As Variant, p As Variant
Set outputLocation = Sheet2.Range("C1")
Set topPartList = Sheet2.Range("A2:A8")
For Each part In topPartList
If Not topPartDict.Exists(part.Value) Then
Set d = New Dictionary
d.Add Key:=part.Offset(0, 1).Value, item:=part.Offset(0, 1).Value
topPartDict.Add Key:=part.Value, item:=d
Set topPartDict(part.Value) = d
Else
Set d = topPartDict(part.Value)
d.Add Key:=part.Offset(0, 1).Value, item:=part.Offset(0, 1).Value
Set topPartDict(part.Value) = d
End If
Next part
indLvl = fPresentParts(outputLocation, topPartDict, topPartDict, 0)
End Sub
Private Function fPresentParts(ByRef location As Range, ByRef tpd As Dictionary, ByRef d As Dictionary, indLvl As Integer) As Integer
Dim k As Variant, v As Variant
Dim subPartsDict As Dictionary
For Each k In d.Keys()
If TypeOf d(k) Is Dictionary Then
Set v = d(k)
location.IndentLevel = indLvl
location.Value = k
Set location = location.Offset(1, 0)
indLvl = indLvl + 1
Set subPartsDict = v
indLvl = fPresentParts(location, tpd, subPartsDict, indLvl)
Else
If tpd.Exists(d(k)) And TypeOf tpd(d(k)) Is Dictionary Then
location.IndentLevel = indLvl
location.Value = d(k)
Set location = location.Offset(1, 0)
indLvl = indLvl + 1
indLvl = fPresentParts(location, tpd, tpd(d(k)), indLvl)
Else
location.IndentLevel = indLvl
location.Value = k
Set location = location.Offset(1, 0)
End If
End If
Next k
indLvl = indLvl - 1
fPresentParts = indLvl
End Function