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

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.

Leibel S

@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

@Lucas Rayala

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

Leibel S

@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)

Lucas Rayala

That's the direction I was heading -- the last column doesn't get me there, however. I tried to sum all the First Group at or prior to the current row with this formula: =SUMIF([First Group]:[First Group], [ROW#]:[ROW#] <= [ROW#]@row)

However, that just populated the column with the sum of every group (17).

Leibel S

@Lucas Rayala

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

Lucas Rayala

Must have been a syntax or cache issue. In the end I just updated the sumif to sumifs (below), that worked. I then went and retried yours, which worked as well. Thanks for the help!

 =SUMIFS([First Group]:[First Group], [ROW#]:[ROW#], <= [ROW#]@row)

Lucas Rayala

Curious why use the ROW# vs just the Row ID? I was able to get both to work.

Leibel S

ROW# will update even if you move around the rows...

Lucas Rayala

Ah, got it. If I have a unique serial number for each line I can use that as well. Thanks!

Leibel S

Yes that would work