A long solution, and I'm not certain that the code is going to display
properly in this posting. Just in case, there's a workbook with the code in
it that you can download from here:
http://www.jlathamsite.com/uploads/ParentChildHeirarchy.xls
To put this code to use:
Open the workbook.
Press [Alt]+[F11] to enter the VB Editor (VBE)
In the VBE, choose Insert | Module
Then copy the code below and paste it into the module.
Make any changes to constants that you may need to - as it's set up,
it is going to put your heirarchy list into columns E, F and G.
Close the VBE.
Save the workbook.
To run it, select the sheet with your list and from the main Excel menu,
choose:
Tools | Macro | Macros and highlight the name of this macro in the list and
click the [Run] button.
Here's the code:
Sub MakeHierarchyList()
'the "input" columns/rows
Const IDColumn = "A"
Const ParentIDColumn = "B"
Const NameColumn = "C"
Const FirstEntryRow = 2
'the "output" area
'the first available column
'assumes all columns to the
'right of this one and all rows
'in it and the others are
'available to put the
'heirarchy list into.
Const firstHColumn = "E" ' change as needed
Const NumberOfLevels = 3 ' change if it differs
'we need a loop for each possible
'level in the heirarchy
'I'm just using 3 levels
Dim level1 As Long ' loop control
Dim level2 As Long ' loop control
Dim level3 As Long ' loop control
Dim level1_ID As Long ' top level ID
Dim level2_ID As Long ' 2nd level ID
Dim lastUsedRow As Long
lastUsedRow = Range(IDColumn & Rows.Count). _
End(xlUp).Row
'clear any previous data in the
'heirarchy display columns
Range(firstHColumn & FirstEntryRow & ":" & _
Range(firstHColumn & FindNextHRow(firstHColumn)). _
Offset(0, NumberOfLevels - 1).Address).Clear
'get to work!!
For level1 = FirstEntryRow To lastUsedRow
If IsEmpty(Range(ParentIDColumn & level1)) Then
'this is a top level parent entry
level1_ID = Range(IDColumn & level1).Value
'put the top level name in 1st available column
Range(firstHColumn & FindNextHRow(firstHColumn)) = _
Range(NameColumn & level1)
For level2 = FirstEntryRow To lastUsedRow
'see if an entry has the level1 ID as
'its parent ID
If Range(ParentIDColumn & level2) = level1_ID Then
'yes, this is a child of level1_ID
level2_ID = Range(IDColumn & level2).Value
'put 2nd level name in proper column/row
Range(firstHColumn & FindNextHRow(firstHColumn)). _
Offset(0, 1) = Range(NameColumn & level2)
For level3 = FirstEntryRow To lastUsedRow
'see if entry has level2 ID as parent
If Range(ParentIDColumn & level3) = level2_ID Then
Range(firstHColumn & FindNextHRow(firstHColumn)). _
Offset(0, 2) = Range(NameColumn & level3)
End If
Next ' end of level3 loop
End If
Next ' end of level2 loop
End If
Next ' end of level1 loop
End Sub
Private Function FindNextHRow(firstCol As String) As Long
'must check all possible columns
'to see last row used in each
'this is set up for 3 columns, but
'you should be able to adapt for more
Const NumberOfLevels = 3 ' change if it differs
Dim HLevelCol As Integer ' loop control
FindNextHRow = 1 ' initialize
For HLevelCol = 0 To NumberOfLevels - 1
If Range(firstCol & Rows.Count).Offset(0, HLevelCol). _
End(xlUp).Row > FindNextHRow Then
FindNextHRow = Range(firstCol & Rows.Count). _
Offset(0, HLevelCol).End(xlUp).Row
End If
Next
'increment the value down to the next row
'which will be first empty row in
'the heirarchy group
FindNextHRow = FindNextHRow + 1
End Function
Mitch Powell said:
Here's a stumper. I have a list that represents parent/child relationships,
as follows:
ID ParentID Name
1 Item 1
2 1 Item 2
3 1 Item 3
4 2 Item 4
5 2 Item 5
6 3 Item 6
7 3 Item 7
Representing the above in a hierachal format would yield the following:
Item 1
Item 2
Item 4
Item 5
Item 3
Item 6
Item 7
The list is very large and I what I need is a mechnism for repsenting the
raw data in the list in a user-friendly hierarchal format. I'm pretty
stumped on this one. Any ideas?
