Search code examples
google-sheetsarray-formulas

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

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.

Solution

  • 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)))})

    enter image description here

    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.