Auto numbering from group and rows in Google Sheets

Question

I am trying to implement a formula that will auto number rows within a group

** OPEN Google Sheets URL **

https://docs.google.com/spreadsheets/d/1Uhy1jLTiozXq1M2S3b0ACjqxv3-pCMC2Rr4BhkuBzfU/edit?usp=sharing

The way I have it now, works but if I add a row in a subgroup it doesnt work:

=ArrayFormula({"1";COLUMNS($B$1:B$1) & "." & ROW($B$1:indirect("A"&counta(A13:A25)))})

and for Phase 2 I have to do this:

=ArrayFormula({"2";COLUMNS($A$1:B$1) & "." & ROW($B$1:indirect("A"&counta(A27:A33)))})

And besides that I have a feeling that the first part ;COLUMNS($B$1:B$1) decide what number is in front of the x.1 So if I use B$1:B$1 it will use 2, but if I do ;COLUMNS($E$1:E$1) it will use 5. I don't think this should be the way.

There are some more notes in the document if anyone is interested

Is there anyone who knows how to fix this numbering?

Answer 1

Well, here's a reusable formula that doesn't depend on you figuring out the row number or doing any tedious editing:

=ArrayFormula(SUBSTITUTE(REGEXEXTRACT(INDIRECT("C"&ROW());"Phase (\d+)")&"."&SEQUENCE(LEN(REGEXREPLACE(SUBSTITUTE(REGEXEXTRACT(TEXTJOIN("|";;INDIRECT("C"&ROW()&":C"));"Phase.*?\|(?:Phase|\|)");"||";"|");"[^|]";));1;0);".0";))

Depending on your locale, you may have to use , instead of ; as argument separators.

Caveats:

• You need to put it next to a cell that starts with "Phase"
• You have to paste it every time you make a new Phase.
• None of your tasks can have a | in them.
• Your table must have at least one blank row following it.

Here's a more readable version:

=ArrayFormula(
    SUBSTITUTE(
        REGEXEXTRACT(
            INDIRECT("C"&ROW());
            "Phase (\d+)"
        )&"."
        &SEQUENCE(
            LEN(
                REGEXREPLACE(
                    SUBSTITUTE(
                        REGEXEXTRACT(
                            TEXTJOIN("|";;INDIRECT("C"&ROW()&":C"));
                            "Phase.*?\|(?:Phase|\|)"
                        );
                        "||";
                        "|"
                    );
                    "[^|]";
                )
            );
            1;
            0
        );
        ".0";
    )
)

Explanation:

1. Stringify the ProjectTitle Column starting on the current row, joining with |'s. The end of the string is a bunch of |s.
2. For a given phase, use a Regex to extract Phase 1|Task 1|...|...Phase 2. If it's the last phase, we extract Phase N|...|| instead, then substitute || with |. This is why it only works on a Phase row.
3. Count the number of |s to determine number of tasks. Do this by removing all non-|'s from the string and getting the length.
4. Generate a Sequence starting at 0 to determine sub-numbering.
5. Replace .0 with empty for phase row.