Child Auto Numbering per Parent Row
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
Best Answer

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:
 Create an autonumber column called Auto and save the sheet to get it to populate
 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)
 Create a text/number column called Parent and enter the following formula, then right click and Convert to Column Formula:
=PARENT(Item@row)
 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))
 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.
Answers

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.

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:
 Create an autonumber column called Auto and save the sheet to get it to populate
 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)
 Create a text/number column called Parent and enter the following formula, then right click and Convert to Column Formula:
=PARENT(Item@row)
 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))
 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.

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.

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))

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 prebaked into it.

I had been working on autonumbering 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!

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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives