How to get Tree View Structure for Huge Data

[Milica Mihac]

Hello,

I have a table/sheet with huge data.I would like to get sometking like Tree
View structure for them if it is possible but I have no idea how.
My table have data about Products structure in two levels.I have 3 columns
Item_number_of_parent, Item_number_of_child, quantity.

For example:

Item_number_of_parent Item_number_of_child Quantity
FG-1 SF-1 5
FG-1 SF-2 8
FG-1 SF-3 3
FG-1 RM-1 1
FG-1 RM-2 1
SF-1 RM-8 3
SF-1 RM-7 2
... etc.


FG is finished goods, SF is semifinished goods, and RM is row material.

So it this possible to get from this data something like bellow?


-FG-1
-SF-1
-RM-8
-RM-7
+SF-2
+SF-3
-RM-1
-RM-2

 

[Joel]

Try this code. Original data is on sheet1 and tree is put on sheet2.


Sub MakeTree()
Dim Child(100)
Dim FindChild(100)
Dim First(100)

Sheets("Sheet2").Cells.Clear
With Sheets("Sheet1")
'Column D will be used as an indicator that the row has been used
.Columns("D").Clear
Sh1RowCount = 1
Sh2RowCount = 1
Sh2ColCount = 1
LastItemNo = ""
Do While .Range("A" & Sh1RowCount) <> ""

If .Range("D" & Sh1RowCount) = "" Then
ItemNo = .Range("A" & Sh1RowCount)
.Range("D" & Sh1RowCount) = "x"
If LastItemNo <> ItemNo Then
Sheets("Sheet2").Cells(Sh2RowCount, Sh2ColCount) = ItemNo
Sh2RowCount = Sh2RowCount + 1
End If
Sh2ColCount = Sh2ColCount + 1
Child(Sh2ColCount) = .Range("B" & Sh1RowCount)
Sheets("Sheet2").Cells(Sh2RowCount, Sh2ColCount) = Child(Sh2ColCount)
Sh2RowCount = Sh2RowCount + 1
First(Sh2ColCount) = True
Do While Sh2ColCount > 1
If First(Sh2ColCount) = True Then
Set FindChild(Sh2ColCount) =
..Columns("A").Find(what:=Child(Sh2ColCount), _
LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByColumns)
First(Sh2ColCount) = False
Else
firstAddress = FindChild(Sh2ColCount).Address
Do
Set c = .Columns("A").Find(what:=Child(Sh2ColCount), _
LookIn:=xlValues, lookat:=xlWhole,
SearchOrder:=xlByColumns, _
after:=FindChild(Sh2ColCount))
If c Is Nothing Then Exit Do
Loop While c.Address = firstAddress
'find when using after loops back to beginning of range
If Not c Is Nothing Then
If c.Row < FindChild(Sh2ColCount).Row Then
'set to nothing
Set c = Intersect(Range("A1"), Range("A2"))
End If
End If
Set FindChild(Sh2ColCount) = c
End If
If FindChild(Sh2ColCount) Is Nothing Then
Sh2ColCount = Sh2ColCount - 1
Else
FindChild(Sh2ColCount).Offset(0, 3) = "x"
Child(Sh2ColCount + 1) = FindChild(Sh2ColCount).Offset(0, 1)
Sh2ColCount = Sh2ColCount + 1
Sheets("Sheet2").Cells(Sh2RowCount, Sh2ColCount) =
Child(Sh2ColCount)
Sh2RowCount = Sh2RowCount + 1
First(Sh2ColCount) = True
End If
Loop
End If
LastItemNo = .Range("A" & Sh1RowCount)
Sh1RowCount = Sh1RowCount + 1
Loop
End With
End Sub

 

[Milica Mihac]

Thank you a lot Joel!

It works just like I want!