Mail merge file help

Hi, I have a fiddly question based on some HR data. I have a filtered list of employees by name and details and then a series of look-up columns that pull through their Manager, Head of Dept, Director and their HR Manager (the management team.) Any of the Management team looks after multiple people and a Director could also be a Head of, or a Manager, a Head of could also be a Manager - example input:

From this list, I want to pull out a unique list of each Manager, Head of, Director and 

HR Manager in one list of the four columns, their emp no. their name, their role and their email address. So far so simple:

I then want to have a series of columns (fields) for each of the Management team rows, that has the employee they look after with their employee details. Because there are four layers of management team, each employee will appear four times. But different members of the management team might have 1 to 10 employees they look after so need 1 to 10 sets of the fields after their name. Example output:

I have attached a file example that shows the input I can provide and the output needed. I have used Unique, Sort, Filter, To Col, VStack etc. but the issue for me is that employees need to appear next to multiple members of the management team. Can anyone crack it?!