Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tree View; selecting and pasting data to cell | Excel Programming | |||
Huge data structure in Excel | Excel Programming | |||
present data as hierarchy/tree structure in user form | Excel Programming | |||
Tree Structure | Excel Programming | |||
Tree Data Structure in VB/VBA | Excel Programming |