Complex Sorting

[PeterAP]

Hi,

I'm having trouble sorting a data set to appear in the exact order I want it. It's not an easy sort and I've been trying to solve this for a while so thought it would be worth reaching out to the community!

1. Rank is the level of the person at Name 1.

2. Name 2 is the boss of Name 1. Except for Peter Parker who is the top boss.

I need to sort the source data to appear as in the Desired Output below, so if there is a multi-level hierarchy, I can clearly see it.

Can anyone help?

[aatalai]

@PeterAP seems more like a joining problem, see workflow attached let me know if it helps

nb going forward attach data sets as texts or files to make it easier to input into alteryx 

[Qiu · Accepted solution]

@PeterAP 
There is a great macro by @NicoleJohnson for this purpose if you need your flow to be dynamic.
https://community.alteryx.com/t5/Community-Gallery/Hierarchy-Generation-Macro/ta-p/880709

[aatalai]

@Qiu didn't know that existed, everyday is a school day

[Qiu]

@aatalai 
Agree on that 😁

[PeterAP]

@Qiu This worked great, thank you. I had to change a tool in the macro to change from count_nonblanks to count all and add on to the workflow to structure it a more more useable format for my use case and developed another iterative macro to strip out lines where the top layers of the hierarchy were no needed, but i got there.

Thanks so much for your help as this macro saved me a lot of time.

[Qiu]

@PeterAP 
Thank you for letting me know it works. 😁