Sequential numbering of Child Rows that restart with each parent row

ChrisUPC

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?

Paul Newcome

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)

Paul Newcome

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)

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=2f98d97c73624e9d870fca5937bd9e95