How to get Tree View Structure for Huge Data
 

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



--
Thanks a lot,
BR, Milica

How to get Tree View Structure for Huge Data
 

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" wrote:

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



--
Thanks a lot,
BR, Milica

How to get Tree View Structure for Huge Data
 

Thank you a lot Joel!

It works just like I want!

--
BR, Milica



"Joel" wrote in message
...
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" wrote:

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



--
Thanks a lot,
BR, Milica