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?

Mitch,

I am attempting a macro for this. Could possibly take a little while...
I'll post it here when it is completed.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


"Mitch Powell" wrote:

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?

I have adapted a macro of mine to fit your sample data. You can download it
he

http://freefilehosting.net/download/40cj1

Hope this helps,

Hutch

"Mitch Powell" wrote:

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?

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 he
http://www.jlathamsite.com/uploads/P...dHeirarchy.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" wrote:

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?

Great job! That was about to make my week if I had gotten that working.
Thanks for saving me the time.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


"JLatham" wrote:

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 he
http://www.jlathamsite.com/uploads/P...dHeirarchy.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" wrote:

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?

Be nice to see if there's an easy way to deal with varying levels of
heirarchy in the Sub than I used. But right now in my mind I'm thinking
you've got to have a minimum number of variables to deal with a limited
number of levels. Maybe with some "pre processing" you could do something
with dynamic arrays.
I'll have to take a look at Tom Hutchins' solution to see if he dealt with
it in a more 'robust' manner than I did.

"Thomas [PBD]" wrote:

Great job! That was about to make my week if I had gotten that working.
Thanks for saving me the time.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


"JLatham" wrote:

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 he
http://www.jlathamsite.com/uploads/P...dHeirarchy.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" wrote:

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?

Good response - it looks like your solution is much more robust than mine,
which is locked into the 3 levels of parent - child - grandchild. Much as
I'd have liked to have worked out given a little more time, but you've saved
me that effort!

If it were up to me, I'd give you the green check for "answered!", as it is,
I find it very helpful ... thanks for sharing.

"Tom Hutchins" wrote:

I have adapted a macro of mine to fit your sample data. You can download it
he

http://freefilehosting.net/download/40cj1

Hope this helps,

Hutch

"Mitch Powell" wrote:

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?