Formula Help for Counting Children

kpierson109

KPH

Is the auto number column the one on the right of the screen shot? The column starting 197?

It looks like you have an extra level of hierarchy, not just one parent and child. Is that correct?

I have run out of hours today but will have a look tomorrow.

kpierson109

Yes, there are multiple parent and child. Would I restart the numbering?

I need the numbering to be based off subsection. So Learn is a child (section of Lesson 3) but it is a parent of Read, Respond, and Observe (subsections). I want to count the number of subsections within a section.

kpierson109

Is it because the subsection names are repeating? It seems like that is the case. If so is there a way for it to see each Learn as the first occurrence?

KPH

We created the parent primary column to hold a unique identifier for each group. If the value in that is not unique to each group the numbers will not start over.

Replace the formula in parent primary with something that is unique to each section. We were using:

=PARENT([Primary Column]@row)

You might want to use another column instead, so the formula would be:

=PARENT([Unique Column]@row)

Or join the values from two columns to create something unique, using a formula like:

=PARENT([Primary Column]@row)+" - "+PARENT([Other Column]@row)

kpierson109

Thank you! That worked!!

=IF(COUNTIF([Parent Primary]:[Parent Primary], [Parent Primary]@row) = 1, "." + SUBSTITUTE(RIGHT(PARENT([Public Facing Title]@row), LEN(PARENT([Public Facing Title]@row)) - FIND("*", SUBSTITUTE(PARENT([Public Facing Title]@row), ".", "*", LEN(PARENT([Public Facing Title]@row)) - LEN(SUBSTITUTE(PARENT([Public Facing Title]@row), ".", ""))))), " ", "-"), "." + COUNTIFS([Parent Primary]:[Parent Primary], @cell = [Parent Primary]@row, [Row #]:[Row #], @cell <= [Row #]@row)))))))))))

Last thing, If I wanted to remove an ending if a parent has resources in it how would I do that?

Example:

al.g1.m1.ta.tg.about-the-images

al.g1.m1.ta.tg.end-notes

al.g1.m1.ta.tg.end-notes.1

al.g1.m1.ta.tg.end-notes.2

al.g1.m1.ta.tg.end-notes.3

al.g1.m1.ta.tg.resources

Instead of al.g1.m1.ta.tg.resources populating, I want ".resouces" removed.

al.g1.m1.ta.tg.

KPH

I am still not following your examples. Can you identify what the parent row is?

You can either add another IF to to formula to do something different if the parent row contains resources (which I think is where it is being pulled from). Or you can add another substitute to the formula to remove ".resources" wherever this appears within the output. This will remove all instances of ".resources".

=SUBSTITUTE(SUBSTITUTE(PARENT([Public Facing Title]@row), " ", "") + IF(COUNTIF([Parent Primary]:[Parent Primary], [Parent Primary]@row) = 1, "." + SUBSTITUTE(RIGHT(PARENT([Public Facing Title]@row), LEN(PARENT([Public Facing Title]@row)) - FIND("*", SUBSTITUTE(PARENT([Public Facing Title]@row), ".", "*", LEN(PARENT([Public Facing Title]@row)) - LEN(SUBSTITUTE(PARENT([Public Facing Title]@row), ".", ""))))), " ", ""), "." + COUNTIFS([Parent Primary]:[Parent Primary], @cell = [Parent Primary]@row, [Row #]:[Row #], @cell <= [Row #]@row)), ".resources", "")

kpierson109

Apologies. If public facing title is equal to "Resources" then I want the output to remove ".resources" which is currently being produced by the formula.

I tried this and moving the substitute around but it is still populating ".resources".

KPH

With the current formula, if the Public Facing Title is equal to Resources then a Single child would be Resources.Resources and multiple children would be Resources.1, Resources.2 etc. You could exclude any rows where the public facing title is equal to "Resources" by using another IF.

=IF(PARENT([Public Facing Title]@row) = "Resources", "", SUBSTITUTE(PARENT([Public Facing Title]@row), " ", "") + IF(COUNTIF([Parent Primary]:[Parent Primary], [Parent Primary]@row) = 1, "." + SUBSTITUTE(RIGHT(PARENT([Public Facing Title]@row), LEN(PARENT([Public Facing Title]@row)) - FIND("*", SUBSTITUTE(PARENT([Public Facing Title]@row), ".", "*", LEN(PARENT([Public Facing Title]@row)) - LEN(SUBSTITUTE(PARENT([Public Facing Title]@row), ".", ""))))), " ", ""), "." + COUNTIFS([Parent Primary]:[Parent Primary], @cell = [Parent Primary]@row, [Row #]:[Row #], @cell <= [Row #]@row)))

This would return blank instead.

However, if you don't mean Public Facing Title is equal to Resources, but that Public Facing Title contains "Resources", then you need a slightly different formula.

=IF(CONTAINS("Resources", PARENT([Public Facing Title]@row)), "", SUBSTITUTE(PARENT([Public Facing Title]@row), " ", "") + IF(COUNTIF([Parent Primary]:[Parent Primary], [Parent Primary]@row) = 1, "." + SUBSTITUTE(RIGHT(PARENT([Public Facing Title]@row), LEN(PARENT([Public Facing Title]@row)) - FIND("*", SUBSTITUTE(PARENT([Public Facing Title]@row), ".", "*", LEN(PARENT([Public Facing Title]@row)) - LEN(SUBSTITUTE(PARENT([Public Facing Title]@row), ".", ""))))), " ", ""), "." + COUNTIFS([Parent Primary]:[Parent Primary], @cell = [Parent Primary]@row, [Row #]:[Row #], @cell <= [Row #]@row)))

Again this will return blank instead.

If you want to return other parts of the Public Facing title you can replace "" with the function for that part.

Does that hekp?