Renumbering Children

Paul Reeves

I am trying to figure out how to set up a column that would set a child's number. If the parent has 5 children then the numbering would be 1-5. If you inserted a child between 2 & 3 then that new record would become 3 and the rest would be 4-6.

I know I have to count the children but I am not sure how to identifies the records position within the order of children.

Paul Newcome

You'll need 3 helper columns.

Auto-Number column called "Auto" in this example (formatting doesn't matter).

Text/Number column called "Row" in this example with this column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Text/Number column called "Parent" in this row with a formula to pull parent row data onto each of the child rows:

=PARENT([Task Name]@row)

Then in the child numbering column you would use:

=COUNTIFS(Parent:Parent, @cell = Parent@row, Row:Row, @cell<= Row@row)

Paul Reeves

This works nicely for one Parent, but if I add a second family (Parent/Child) the Children numbering is not resetting.

Paul Newcome

Are you able to post some screenshots with sample data?

Paul Reeves

Paul - I just started over with a fresh worksheet and was able to get it working.

Thank you.

Paul

Paul Newcome

Happy to help. 👍️