Flattening Org File

[tearless75]

Hi, I have a file looks like below

Column A  Column B

1                2

2                3

1                4

2                5

5                6

It should be converted into 

Column A  Column B  Column C  Column D

1                2                5                6

1                2                3

1                4

Challenging part is that I don't know how many columns I would need in the output since the input file is pretty large and do not know how many level there would be in the source. 

Can someone please help?

[CailinS]

@tearless75 I can't imagine this is the only (or even best way) to do this, but I was able to solve with an Iterative Macro. I ran out of time to perfect my workflows so it may seem a little confusing. I've attached a Full Workflow - no iterative.yxzp (it contains a workflow saved as a .yxmc that isnt actually a macro, but does contain the macro I built). I've also included some screenshots so you can see how I first built out the iterative process manually to confirm my logic before taking part of the workflow and turning it into an iterative macro. 

 

In short, I needed to identify the first level of leader (1, in this case) and then use that as the first 'iterative' input for my macro + the original file as the 'look up' data (which stays the same throughout and doesn't iterate. Then I proceed to 'Join' on the employees of each manager iteration after iteration. I use a handy Iterative Macro feature, called the Iteration Number, to dynamically rename the manager levels as I continue through. The final employee does get renamed to Manager as again I ran out of time to perfect, but hopefully this shows you how it can be done.

[Qiu]

@tearless75 
I always use this macro 

https://community.alteryx.com/t5/Community-Gallery/Hierarchy-Generation-Macro/ta-p/880709

[CailinS]

Great find, @Qiu I felt like I remembered something like it but didn't locate it (and I admit I wanted to dust off my iterative macro skills 😅)

[Qiu]

@CailinS 
Actually, its simply because I am bit lazy 😁
Good to practice the iterative macro skills.