Sequential numbering of Child Rows that restart with each parent row
I have a sheet with a parent row (PO number) and multiple child rows (parts ordered per PO number) that I am trying to auto-assign a number starting with 1, to the child rows of that parent only so I can use it to populate another sheet based on PO number AND that auto number. Is there a formula that can do that?
I've created a helper column [Item Number] that assigns a "1" to child rows and a "0" to the parent row. Then I have a second column [Item #] with the formula =SUM(PARENT([Item Number]@row):[Item Number]@row)) to assign 1,2,3,4,5,6... but I get an #UNPARSABLE error. If I use the formula =SUM($[Item Number]$1:[Item Number]@row)) IT works but doesn't start over after the next parent row. What am I missing?
Best Answer
-
Insert an auto-number column (called "Auto" in this example). Formatting doesn't matter. Then you will need a text/number column (called "Row" in this example) with the following column formula:
=MATCH(Auto@row, Auto:Auto, 0)
Next you will need another helper column (called "Parent" in this example) with the following column formula:
=PARENT([Primary Column Name]@row)
Then to get your sequential numbering in a text/number column you would use:
=COUNTIFS(Parent:Parent, @cell = Parent@row, Row:Row, @cell<= Row@row)
Answers
-
Insert an auto-number column (called "Auto" in this example). Formatting doesn't matter. Then you will need a text/number column (called "Row" in this example) with the following column formula:
=MATCH(Auto@row, Auto:Auto, 0)
Next you will need another helper column (called "Parent" in this example) with the following column formula:
=PARENT([Primary Column Name]@row)
Then to get your sequential numbering in a text/number column you would use:
=COUNTIFS(Parent:Parent, @cell = Parent@row, Row:Row, @cell<= Row@row)
-
Hi @ChrisUPC
I would use the RANKEQ function.😀
=IF(NOT(isParent@row), RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], [PE Row ID]:[PE Row ID], [PE Row ID]@row), 1))
For details, check the published sheet below.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!