How to produce tree view from parent/child list?

masai_chadi

New Member
Joined
Sep 29, 2006
Messages
5
I have a list of over 10,000 employee with 7 levels of employee- supervisor relationship from our HR system. The list looks like:

Joe reports to Mary
Sandar reports to Mary
Peter reports to Philip
Viola reports to Mary
Mary reports to David
Saly reports to Mary
Philip reports to David

Parent and Children are stored in different columns (say columns A & B).

I would like to produce a tree view, in another tab, that looks like:

David (column A)
--Mary (column B)
----Joe (column C)
----Sandra (column C)
----Saly (column C)
----Viola (column C)
--Philip (column B)
----Peter (column C)

How can I achieve this?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, masai_chadi
Welcome to the Board !!!!

try this series of operations
during the process you could copy the sheet for each step

1. replace "reports to " by nothing
  A             
1 Joe Mary      
2 Sandar Mary   
3 Peter Philip  
4 Viola Mary    
5 Mary David    
6 Saly Mary     
7 Philip David  

sheet1

[Table-It] version 06 by Erik Van Geit
2. apply Text To Columns
  A      B      
1 Joe    Mary   
2 Sandar Mary   
3 Peter  Philip 
4 Viola  Mary   
5 Mary   David  
6 Saly   Mary   
7 Philip David  

sheet1

[Table-It] version 06 by Erik Van Geit
3. swap columns
  A      B      
1 Mary   Joe    
2 Mary   Sandar 
3 Philip Peter  
4 Mary   Viola  
5 David  Mary   
6 Mary   Saly   
7 David  Philip 

sheet1

[Table-It] version 06 by Erik Van Geit
4. add formulas
  A      B      C     
1 Mary   Joe    FALSE 
2 Mary   Sandar FALSE 
3 Philip Peter  FALSE 
4 Mary   Viola  FALSE 
5 David  Mary   TRUE  
6 Mary   Saly   FALSE 
7 David  Philip TRUE  

sheet1

[Table-It] version 06 by Erik Van Geit
Code:
RANGE FORMULA (1st cell)
C1:C7 =ISNA(MATCH(A1,$B$1:$B$7))

[Table-It] version 06 by Erik Van Geit
5. sort by last column and insert header
  A      B      C      
1 header header header 
2 Mary   Joe    FALSE  
3 Mary   Sandar FALSE  
4 Philip Peter  FALSE  
5 Mary   Viola  FALSE  
6 Mary   Saly   FALSE  
7 David  Mary   TRUE   
8 David  Philip TRUE   

sheet1

[Table-It] version 06 by Erik Van Geit
6. filter to FALSE and move visible range 1 column to the right
show again all data
  A      B      C      D     
1 header header header       
2        Mary   Joe    FALSE 
3        Mary   Sandar FALSE 
4        Philip Peter  FALSE 
5        Mary   Viola  FALSE 
6        Mary   Saly   FALSE 
7 David  Mary   TRUE         
8 David  Philip TRUE         

sheet1

[Table-It] version 06 by Erik Van Geit
7. select range press F5 "special" formulas ==> clear
8. sort: in this case key1 = column B, key2 = column A, key3 = column C
  A      B      C      
1 header header header 
2 David  Mary          
3        Mary   Joe    
4        Mary   Saly   
5        Mary   Sandar 
6        Mary   Viola  
7 David  Philip        
8        Philip Peter  

sheet1

[Table-It] version 06 by Erik Van Geit

to my sense inserting the columns is not that difficult, but you might need some thinking for the final sort

kind regards,
Erik
 

masai_chadi

New Member
Joined
Sep 29, 2006
Messages
5
Is it possible to automate this as I many have 7-8 levels deep relationship in two columns with over 10,000 employees?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Is it possible to provide some feedback before posting the next question?
did this method work for you ... ?
 

masai_chadi

New Member
Joined
Sep 29, 2006
Messages
5
Sorry, I should have. This method did work, and produced the desired results. As I mentioned, the list of employees with their supervisors is very large and deep. I was wondering if the solution can be automated. Is it possible to write a recursive macro/vb code to achieve the same?
 

Forum statistics

Threads
1,141,599
Messages
5,707,311
Members
421,502
Latest member
PULBAG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top