Need help in arranging the data in excel as per sample

[ans511]

Hi There

I have data in one work sheet where I have three column , First column has data of Manager third column has data of sub ordinate second column is just the info that first column person manages the third person.

I have some 2000 odd lines in my data .

The out put I am looking for is to identify who the parent is of the group and arrange the data in hireachy.

The challenge here is the Manager who is in column 1 can also be a sub ordinate to another manager and we do not know how many level in the data.

For example

A manages B
B manages C
and D manages A then the data I have is as below

A supervise B
B supervise C
D supervise A
A also Supervise E
E supervise Q
The out put I am looking for is

D=>A=>B=>C
D=>A=>E=>Q

Please refer attached spreadsheet for sample

Thanks in advance

 

[Marc L]

Some misunderstood : I did not ask to open a new thread
but I just tried to explain why you did not have any answer …

I will take a glance to code a starter.
​

 

[Marc L]

As a starter (must activate Microsoft Scripting Runtime reference) :​

Dim oDicS As New Dictionary

Sub DChild(VKEY, COL&, NROW&)
        SP = Split(oDicS.Item(VKEY))
    For C& = 1 To UBound(SP)
        Sheet2.Cells(NROW, COL).Value = SP(C)
        If oDicS.Exists(SP(C)) Then DChild SP(C), COL + 1, NROW Else NROW = NROW + 1
    Next
End Sub

Sub Demo0()
    With Sheet1.UsedRange.Rows
        If .Count = 1 Then Beep: Exit Sub
        VA = Application.Index(.Value, Evaluate("ROW(2:" & .Count & ")"), [{1,3}])
    End With
        Sheet2.Cells(8).CurrentRegion.Clear
    For R& = 2 To UBound(VA)
        oDicS.Item(VA(R, 1)) = oDicS.Item(VA(R, 1)) & " " & VA(R, 2)
    Next
        Sheet2.[H2:I2].Value = Array(VA(1, 1), VA(1, 2))
         If oDicS.Exists(VA(1, 2)) Then DChild VA(1, 2), 10, 2
            oDicS.RemoveAll
        Set oDicS = Nothing
        Application.Goto Sheet2.[H1]
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[ans511]

Thanks Marc, Will it work for larger data set or only for the sample .

Also i am assuming only one pre requisute for this code to work i.e to ust activate Microsoft Scripting Runtime reference

 

[Marc L]

Whitout reference activated, a compilation code error occurs …

As the code was made upon attachment so the rule is :

• poor attachment, poor code
• average attachment, average code
• good attachment, pretty good enough code
• excellent attachment, robust code …​

The better initial post explanation with the better attachment,
the better code !

If my code won't work with your real workbook,
so you can guess your attachment level !
But as you yet have the logic within my code,
you can mod it to fit your real data …
​

 

[ans511]

Thanks its working in my work data ...Thanks a ton ..