Batching sequential rows that are "like groups" with a formula

Options
Lucas Rayala
Lucas Rayala ✭✭✭✭✭✭

Here's what I want to do. Column A has values that I want to group by rows, as shown in Column B. So the first group of "x" rows is a "1", the second group of "y" rows is a "2", alternating as shown.

This is a lot of data so I can't have infinite helper columns.

Best Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @Lucas Rayala

    Add the following columns:

    Title: Row ID (system column Auto-Number)

    Title: ROW# Formula: =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

    Title: Value (Column A in your description)

    Title: First Group Formula: =IF([ROW#]@row = 1, 1, IF(INDEX(Value:Value, [ROW#]@row - 1) = Value@row, 0, 1))

    Title: Group Number Formula: =COUNTIFS([First Group]:[First Group], @cell = 1, [ROW#]:[ROW#], @cell <= [ROW#]@row)

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @Lucas Rayala

    did you copy my formulas? Working perfectly on my end:


Answers