Two Columns with Hierarchy Data needs to be split into separate columns
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have a data set that contains one first column all the names of the employees and on the second column all the managers.
What I am trying to achieve: Manager columns include multiple hierarchy and I want to split them into multiple columns.
I think the example will better phrase my question. So here we go:
Here is the sample data:
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 |
And here is the 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 |
Any help or suggestion is appreciated. I tried self joining the data, but I kept losing the employees who are not Managers.
Thanks in advance.
- Labels:
- Batch Macro
- Custom Tools
- Output
- Parse
- Regex
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Like and mark it as a solution if your problem solved
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @partht
I did self join and am able to get the desired results. In your question, you have mentioned about employees who are not managers are getting dropped. So Is there any other dataset where you are facing this issue? Please let me know if this solution answers your question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey Guys! @Qiu @Amol_Telore @Neer_0135
Thank you for your responses.
All of your solutions are helpful and solves the problem I presented in the sample data. But the original dataset contains multiple levels of hierarchy.
I am trying to combine and use parts of your solutions to get the desired result.
In case, I don't get it. I will replicate my original data into the sample data and see if you could help me. Thanks All!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for the feedback.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey All!
I am back after a quite a few tries and need further help. I have attached a workbook with Sample input data and desired output.
Please let me know if you guys could create a workflow to result the desired output. Thanks guys and gals.
