Reverse the order of ancestors

Lian

I printed the ancestors of the task and the task itself to generate a trail of the task. However I want to reverse the order of the ancestors in order to start with the task then its parents, the grandparent, and the great grandparents etcetera all the way up until the first ancestor. For example, Paint -> 2. Main House / Retreat Space -> Renovation & Design instead of Renovation & Design -> 2. Main House / Retreat Space -> Paint so one can trace what each task is for. How do you reverse the order of the ancestors so that it shows the task, then its successive parents and grandparents etc? There are varying levels of ancestors in this dataset. Appreciate any help you can provide! Thank you.

Paul Newcome

You will need some helper columns. One for each level until you have enough for the maximum number of levels.

In each column you would use the same formula but with minor tweaks to output the text from each level.

=IFERROR(INDEX(ANCESTORS([Primary Column]@row), 1), "")

The above will output the top most level/ancestor. Changing the 1 to a 2 will output the next level down, so on and so forth. make sure the columns are arranged in the order you want them in then use a JOIN/COLLECT to pull them all together. You can add an IF to the start to only output the JOIN on child rows if needed as well.

=JOIN(COLLECT([First Helper]@row:[Last Helper]@row, [First Helper]@row:[Last Helper]@row, @cell <> ""), "delimiter") + "delimiter" + [Primary Column]@row

Lian

Thank you! I'll give it a try

Paul Newcome

I just realized I made a little bit of a mistake in the order.

=[Primary Column]@row + "delimiter" + JOIN(COLLECT([First Helper]@row:[Last Helper]@row, [First Helper]@row:[Last Helper]@row, @cell <> ""), "delimiter")