Incrementing a column in a group using an Array Formula in Google Sheets

Question

I am having difficulties trying to get an array formula to increment a column and restart the increment whenever a condition has been met.

Here is an example of what I am trying to achieve, but this has been done manually. It would be great if this could work in an array formula as we'll be adding more rows and would rather not drag the formula down.

[Update based on feedback of the original post]

Column A contains a list of names and teams.
Names are already organised into teams.
The aim is to go through each row and provide a name with an index starting from 1.
Then when a new team is found in column A the index starts again from 0.
The aim is to make this an Array Formula to avoid having to manually re-add the formula when more rows are added.

Link to sheet: https://docs.google.com/spreadsheets/d/1K00LRFNrN99fFXQO1tzp8jjZeCuTxwENXNGuLj0K0ao/edit?usp=sharing

Example

Any thoughts or advice on this would be greatly appreciated.

I don't understand your starting point or your end goal. What I see in your sheet currently doesn't "increment[ing] a column and restart[ing] the increment whenever a condition has been met." What condition? How does one know if it has been met? Where are the columns that have been incremented? Please give more detail, including showing in your sheet your unprocessed data as you will enter it, and then also showing what should happen with that data with a clear explanation of why. — Erik Tyler
Apologies. I have gone back and update my original post. Thank you for your feedback. — herbertsworld
There is still not enough information, since it would seem your dummy/temporary data is not realistic. Will all of your team names include the word "Team"? If not, how would Sheets be able to distinguish between which strings are "Team" names and which strings are "Name" names? — Erik Tyler
Thanks for the feedback. This would be the genuine use of this sheet where the groups all have the same name. I've managed to do something with this in B column and turning on iterative calculation: =ARRAYFORMULA(IF($A2:$A = "TEAM", 0, ADD(OFFSET($B2:$B,-1,0),1))) But this doesn't update the full range correctly unless I keep deleting the generated content — herbertsworld
Given that "Team" will literally appear in each "Team" row of A2:A, I've added a solution below and to your sample spreadsheet. — Erik Tyler

Erik Tyler Erik Tyler · Accepted Answer · 2021-07-13T08:20:07

I've added a new sheet ("Erik Help") to your sample spreadsheet. It contains the following formula in B1:

=ArrayFormula({"ID Index"; IF(A2:A="",, IF( REGEXMATCH(LOWER(A2:A),"team"), 0, ROW(A2:A) - VLOOKUP(ROW(A2:A), FILTER(ROW(A2:A), REGEXMATCH(LOWER(A2:A),"team")), 1, TRUE)))})

The header text is included in the formula. You can change it as you like.

If a cell in A2:A is blank, the corresponding cell in B2:B will be as well.

If REGEXMATCH finds a match with "team" in the lowercase rendering of a cell in A2:A, 0 will be returned for the corresponding cell in B2:B.

Otherwise, VLOOKUP will lookup each remaining row number in a FILTERed array containing only those row numbers where the word "team" appears. (In your sample set, that will be 2, 8, 12). When the exact match is not found (which it will not be for any remaining row), TRUE tells VLOOKUP that, since the search array is in strict ascending order, we want it to "bump back" to the most recent value found. In each case, the returned row number of the most recent row containg "team" is then subtracted from the actual row number to produce the recurring incremental numbering 1, 2, 3, etc.

Incrementing a column in a group using an Array Formula in Google Sheets

2 Answers