Child Auto Numbering per Parent Row

Lamont_norberto

I am trying to find a way to auto generate sequential row IDs for the Child rows of each Parent row. We are doing a sheet per project, with all departments on the sheet, so ideally looking for a column that will auto generate a row ID based on the row position beneath the parent row.

Example:

CNC Item A1
CNC Item A2

Paint Item A
Paint Item B

etc.

I've done some searching but haven't found a solution that works for what I'm looking for. Any help would be appreciated.

Thanks

Brian_Richardson

Hi Lamont, you can do this by getting a row number column, then ranking the collection of rows under each parent.

I'm assuming here that the names (like CNC, Fab, etc) are in a column called "Item". Change the formulas to match whatever your real column name is.

Also, of course, you can make the column names be anything you want instead of my suggestions below.

So setup is:

1. Create an autonumber column called Auto and save the sheet to get it to populate
2. Create a text/number column called Row Number and enter the following formula then right click and Convert to Column Formula: =MATCH(Auto@row,Auto:Auto,0)
3. Create a text/number column called Parent and enter the following formula, then right click and Convert to Column Formula: =PARENT(Item@row)
4. Create a text/number column called "Child Number" and enter the following formula, then right click and Convert to Column Formula: =IF(Parent@row <> "", RANKEQ([Row Number]@row, COLLECT([Row Number]:[Row Number], Parent:Parent, Parent@row), 1))
5. Optional: If you want to construct a text identifier for the child rows like "Parent name Item Sequential Number" then you can add another column called something like "Child Reference" and enter the following formula, then right click and Convert to Column Formula: =IF([Child Number]@row <> "", Parent@row + " Item " + [Child Number]@row)

Here's how this works:

• The Autonumber column applies a sequential number to all rows, regardless of parent or child, regardless of whether the rows have been moved or not.
• The Row Number column finds the current row's Autonumber in the Autonumber column and returns it's relative "position" in that column…which equates to the row number. For example if it is Autonumber 8 in the sheet screenshot below, the relative position of the number 8 in the Auto column is the 6th position, or row 6.
• The Parent column simply gets the name of the Parent of any child. If it's not a child (it has no parent), then it's blank.
• The Child Number column then puts that together. It collects a list of Row Numbers where the parent is the same as the current row's parent, then ranks those row numbers. So, the lowest row number under the parent will be 1, the next 2, etc. It doesn't matter what the actual row numbers are.
• The Child Reference optional column uses the parent and child number to put together a text string.

Jake Gustafson

I'm intrigued by this. Not sure I have an answer, but what if you had an auto numbered column, then another column that used a formula to concatenate the work center to the unique identifier number. Assuming you've got that indicator (CNC, Fab, Weld, Paint, Assy) in another column. Maybe that'll get the creative juices flowing or someone else will swoop in with a solution.

Brian_Richardson

Hi Lamont, you can do this by getting a row number column, then ranking the collection of rows under each parent.

I'm assuming here that the names (like CNC, Fab, etc) are in a column called "Item". Change the formulas to match whatever your real column name is.

Also, of course, you can make the column names be anything you want instead of my suggestions below.

So setup is:

1. Create an autonumber column called Auto and save the sheet to get it to populate
2. Create a text/number column called Row Number and enter the following formula then right click and Convert to Column Formula: =MATCH(Auto@row,Auto:Auto,0)
3. Create a text/number column called Parent and enter the following formula, then right click and Convert to Column Formula: =PARENT(Item@row)
4. Create a text/number column called "Child Number" and enter the following formula, then right click and Convert to Column Formula: =IF(Parent@row <> "", RANKEQ([Row Number]@row, COLLECT([Row Number]:[Row Number], Parent:Parent, Parent@row), 1))
5. Optional: If you want to construct a text identifier for the child rows like "Parent name Item Sequential Number" then you can add another column called something like "Child Reference" and enter the following formula, then right click and Convert to Column Formula: =IF([Child Number]@row <> "", Parent@row + " Item " + [Child Number]@row)

Here's how this works:

• The Autonumber column applies a sequential number to all rows, regardless of parent or child, regardless of whether the rows have been moved or not.
• The Row Number column finds the current row's Autonumber in the Autonumber column and returns it's relative "position" in that column…which equates to the row number. For example if it is Autonumber 8 in the sheet screenshot below, the relative position of the number 8 in the Auto column is the 6th position, or row 6.
• The Parent column simply gets the name of the Parent of any child. If it's not a child (it has no parent), then it's blank.
• The Child Number column then puts that together. It collects a list of Row Numbers where the parent is the same as the current row's parent, then ranks those row numbers. So, the lowest row number under the parent will be 1, the next 2, etc. It doesn't matter what the actual row numbers are.
• The Child Reference optional column uses the parent and child number to put together a text string.

Lamont_norberto

https://community.smartsheet.com/discussion/comment/445273#Comment_445273

Hi Brain

Thank you for the response, it worked perfectly first try. I was also able to change it to fit with some existing columns I already had doing some of the same things. And thank you for the explanation of what everything is doing, also very helpful.

If it's not too much to ask, is it possible to adapt this to include children of child rows? So something like "CNC Item 1.1 / 1.2" etc.

Brian_Richardson

Yes you could add an IF statement that looks at the number of ancestors to know where you are. If the count of ANCESTORS is 0, you know you're at the top level. If 1, you're a child. If 2, you're a child's child.

So using that, you could put together:

=IF(COUNT(ANCESTORS(Item@row)) = 1, Parent@row + " Item " + [Child Number]@row, IF(COUNT(ANCESTORS(Item@row)) = 2, Parent@row + " Item " + PARENT([Child Number]@row) + "." + [Child Number]@row))

Brian_Richardson

Note that it starts getting more convoluted if you need to go past 2 children. For that you're really talking about a WBS structure. If you search Community you'll find there's a number of articles on putting together a WBS. There's also a template in the template gallery with the WBS formulas pre-baked into it.

Tiffany Ross

I had been working on auto-numbering 3 levels in my sheet for almost 6 hours today while searching for support in the community. FINALLY I came across this article which allowed me to removed 12 columns of confusing formulas! I can't thank you enough!

lwells

Good afternoon Brian,

I was wondering if you could help me. I am so thankful for you above breakdown, I think it is the answer that I have been looking for. But I can't get the formula to work. Can you help me?

Columns:

Parent Formula I have:

Child Number Formula I have: I think it is this formula that is throwing the NO Match for both Child # and Child Ref.

Child Reference Formula I have:

End result:

The report was create prior to using Parent/Child rows, and I am going back and adding the child rows but I need them to have the same DA | Closing ID # ( I will hide all the Auto - Child Ref Col), and I would like the Child # to be CRG | DA - 1004.1, 1004.2, 1004.3 etc.

I appreciate any help you can provide. Thank you in advance

Lisa Wells