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 autoassign 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 autonumber 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 autonumber 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
Check out the Formula Handbook template!