Is it possible to create uniquely identified numbers using level 1 parent tasks as a baseline?

Colin Hutton

Is it possible to pull the first three letters of a parent task, and then give it an unique identifying number xxx-9999?

I have groups of tasks that I would like to create an ID for whenever there is a newly created line, each line would inherently be a child to parent task. For example a subtask of an "Engineering" group might read:

Eng-0001

Right now I have the parents nested in a [Task] column, it would be great to have some logic surrounding if it is, or is not a level 1 parent, so it doesn't automatically number parent tasks.

Paul Newcome

Let's try this...

Insert an auto-number column (no special formatting needed).

Next insert a text/number column called "Row Number" with the following column formula:

=MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)

Then we need to insert a text/number column we can call "Parent Helper" and use this column formula:

=LEFT(PARENT(Task@row), 3) + "-"

Now we can insert a text/number column to record your unique ID and start working out that formula. This should work for you:

=IF(COUNT(ANCESTORS(Task@row)) < 0, [Parent Helper]@row + IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 10, "000", IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 100, "00", IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 1000, "0"))) + COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row))

Paul Newcome

Sorry about that. I used the wrong argument in the very first IF.

=IF(COUNT(ANCESTORS(Task@row)) > 0, [Parent Helper]@row + IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 10, "000", IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 100, "00", IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 1000, "0"))) + COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row))

Paul Newcome

Let's try this...

Insert an auto-number column (no special formatting needed).

Next insert a text/number column called "Row Number" with the following column formula:

=MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)

Then we need to insert a text/number column we can call "Parent Helper" and use this column formula:

=LEFT(PARENT(Task@row), 3) + "-"

Now we can insert a text/number column to record your unique ID and start working out that formula. This should work for you:

=IF(COUNT(ANCESTORS(Task@row)) < 0, [Parent Helper]@row + IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 10, "000", IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 100, "00", IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 1000, "0"))) + COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row))

Colin Hutton

This is really quite incredible Paul, you're a Wizard. I followed this the best I could. I now have columns that have all the nomenclature set up. The last formula though returned at first a #[Blocked] I believe is what it said, but when I refreshed it just disappeared and nothing is showing up in the final column. Would you happen to know what is causing this?

Really I can't thank you enough for the help! I've see you on other posts giving this level of detailed insight, it's downright awesome.

Paul Newcome

Sorry about that. I used the wrong argument in the very first IF.

=IF(COUNT(ANCESTORS(Task@row)) > 0, [Parent Helper]@row + IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 10, "000", IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 100, "00", IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 1000, "0"))) + COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row))

Colin Hutton

An easy mistake to make! Thank you so much for the walkthrough on this.

Paul Newcome

Happy to help. 👍️