Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 3rd 08, 04:16 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 37
Default Hierarchal view of parent/child list

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?

  #2   Report Post  
Old October 3rd 08, 06:09 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 154
Default Hierarchal view of parent/child list

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?

  #3   Report Post  
Old October 3rd 08, 06:14 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,069
Default Hierarchal view of parent/child list

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?

  #4   Report Post  
Old October 3rd 08, 06:19 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 3,365
Default Hierarchal view of parent/child list

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?

  #5   Report Post  
Old October 3rd 08, 06:22 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 154
Default Hierarchal view of parent/child list

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?



  #6   Report Post  
Old October 3rd 08, 06:37 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 3,365
Default Hierarchal view of parent/child list

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?

  #7   Report Post  
Old October 4th 08, 01:16 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 3,365
Default Hierarchal view of parent/child list

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?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table - Parent to Child Data Brennan Excel Discussion (Misc queries) 1 September 24th 08 05:23 AM
Finding loops in parent/child relationships GrahamC Excel Discussion (Misc queries) 3 November 22nd 07 05:15 PM
finding multiple parent-child relationships? Keith R Excel Worksheet Functions 4 April 16th 07 04:13 PM
Windows browser Parent/Child structure ERK New Users to Excel 2 December 3rd 06 12:44 AM
Sorting Parent Child kcmtnbiker Excel Worksheet Functions 2 March 31st 06 01:54 AM


All times are GMT +1. The time now is 06:25 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017