How to produce tree view from parent/child list?

[masai_chadi]

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?

 

[erik.van.geit]

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

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]

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]

Is it possible to provide some feedback before posting the next question?
did this method work for you ... ?

 

[masai_chadi]

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?