How do I create a pivot that loops back on itself?

[naninamu]

Hi - I'm not sure if this is possible but here's my scenario.
I have a simple file with data in the following form:

UserName | User Dept | ManagerName | Manager Dept

I'm trying to create a pivot table to represent the org structure, ultimately to drill right down to work out which Departments sit under various Executive Managers (they can have many)

Column 1 is the list of Manager's names
Column 2 is their Department
Column 3 is the UserName of everyone who reports to them
Column 4 is the dept of those Users

The issue is I then want to repeat this - ie somehow transform Column 3 to now be Manager's names, so I can then drag in the User's names beside them, then change those User's into Managers - and repeat until a person does not have anyone reporting to them.

is there anyway to "convert" the column from being a list of Users to a list of Managers? Or am I going about this the wrong way?

Thanks in advance!!