Forum Discussion

Preston Tesvich's avatar
Preston Tesvich
Copper Contributor
Feb 02, 2018

Performing count and max commands on multiple groups of rows

Let's say you have rows: 0.000 0.000 0.000 5.000 6.000 10.000 0.000 0.000 0.000 5.000 ... There is no consistency between the sets, just groups of zeros and non-zeros.  I need a se...
Formulas & Functions
tips and tricks
Willy Lau's avatar
Willy Lau
Iron Contributor
Feb 03, 2018

AFAIK, as you have over 1,000,000 rows, and inconsistent group size, a single formula in column b will have slow performance.  Hence, I suggest to use a helper column, which keep the first row number of the first cell of the current group, which I keep in Column D.

 

In order to make it easier to explain, I create a name, SameGroupAsPrevious

=NOT(OFFSET(Sheet1!$A1, -1, 0)) = NOT(Sheet1!$A1)

Not(anything, except zero) will be true false, and Not(zero) will be true.  If the not result of previous row equals to the not result of this row, this row is in the same group as previous row.

 

Also, I create a name, SameGroupAsNext

=AND(NOT(Sheet1!$A1) = NOT(OFFSET(Sheet1!$A1, 1, 0)), OFFSET(Sheet1!$A1, 1, 0) <> "")

If the not result of this row and that of the next one is the same, and this row is not the last row (next row is not empty, then, this is not the last row), it is in the same group with the next row.

 

In addition, create a name, ThisGroupData

=INDIRECT("A" & INT(Sheet1!$D1) & ":A" & ROW())

Base on the first row number in Column D, we can get the group data from that row to the current row.

 

Now, I put the following formula in Column D

=IF(ROW()=1,1,IF(SameGroupAsPrevious, OFFSET(D1, -1, 0), ROW()))

If current row is 1, first row is 1. Otherwise, if in the same group as previous, keep its first row in Column D in this row.  if not in the same group, obviously, this is the first row of the current group, and hence, put the current row number in column D.

 

In Column B, 

=IF(SameGroupAsNext, "", COUNT(ThisGroupData))

If this group has next data, do not need to show the count, otherwise, show the count of this group. 

 

In Column C

=IF(A1 = 0, "", IF(SameGroupAsNext, "", MAX(ThisGroupData)))

If column A is 0, do not need to show the max.  Otherwise, if this is the last row of this group (SameGroupAsNext = FALSE), find the max. number.

 

 

However, any changes in column A, B, C, D, and those names formula, it will take you some time to recalculate the results. 

Resources