How can I transform table 1 into the desired output (table 2) using Powerpivot:
Table 1:
| Employee | Manager |
|---|---|
| Alexis | Cassie |
| Nick | Dave |
| Benjamin | Dave |
| Amanda | Pheobe |
| John | Pheobe |
| Mary | Pheobe |
| Jackie | Pheobe |
| Tom | Justin |
| Dave | Jackson |
| Pheobe | Jackson |
| Justin | Jackson |
| Cassie | Jackson |
| Lex | Jackson |
| Darlene | Lex |
| Daniel | Lex |
Desired Output:
| Manager - I | Manager - II | Employee |
|---|---|---|
| Jackson | Dave | Nick |
| Jackson | Dave | Benjamin |
| Jackson | Pheobe | Amanda |
| Jackson | Pheobe | John |
| Jackson | Pheobe | Mary |
| Jackson | Pheobe | Jackie |
| Jackson | Justin | Tom |
| Jackson | Cassie | Alexis |
| Jackson | Lex | Darlene |
| Jackson | Lex | Daniel |
CodePudding user response：
so here is the solution, I am sharing with you the formulas so that you should implement them yourself,
- add a new column in your data and check whether an employee name exists in the manager column
- in the solution table start from column 3 i.e employee and get all employees from the data table who are not managers
- Do Index Match or Xlookup, whatever you like to get manager I and manager II names.
