CODEWITHSUNDEEP
excelvbaformulaexcel-2016autonumber
win w
win w

Reputation: 13

Excel - how to get auto number in levelling columns?

I want to count on the data in same columns and restart the count if there have new data in upper level (say previous column), see my picture.

enter image description here

I use the formula of counta, but the numbering is incorrect (say row 8 in example)

=COUNTA(D$3:D8)&COUNTA(E$3:E8)&COUNTA(F$3:F8)&COUNTA(G$3:G8)&COUNTA(H$3:H8)&COUNTA(I$3:I8)

Is it possible to solve by vba?

Upvotes: 1

Views: 116

Answers (1)

David Leal
David Leal

Reputation: 6769

I assume no Excel version constraints as per the tags in the question. You can try the following formula in B2. The number of levels is variable so you can have more than 6.

=LET(x, N(C2:H10<>""), seq, SEQUENCE(ROWS(x)), levels, COLUMNS(x),
 CNTS, LAMBDA(i, LET(A, IF(i=1, N(seq<0),INDEX(x,,i-1)), B, INDEX(x,,i),
   SCAN(0, seq, LAMBDA(ac,s, IF(INDEX(A,s)>0,0,ac) + INDEX(B,s))))),
 REDUCE(CNTS(1), SEQUENCE(levels-1,,2), LAMBDA(ac,z, ac & CNTS(z))))

Here is the output: excel output

It is an array formula, so it spills the entire result. The name x, generates 1's from the grid: C2:H10 if the value is not empty, otherwise 0's. It will be our main input for the calculation. The main idea is to build a cumulative calculation with "memory", i.e. we need to take into consideration the information from the previous column.

In order to build this cumulative calculation with "memory" we use a user LAMBDA function CNTS, to calculate the counts for a given column (i). The CNTS takes into account the previous column (i-1) to reset the accumulator (ac). We use SCAN to do the cumulative sum and reset the accumulator (ac) if the row of the previous corresponding column is positive, by doing this way we can start the counter again.

The rest is just to do a recursive concatenation via REDUCE for all the levels.

Notes:

  1. We could have the case of the first column (no previous information) as a simpler SCAN call, but it can be generalized to consider all cases within the same CNTS function. Therefore we have an IF condition for the case the column is 1, so we create on the fly an artificial previous column via N(seq <0) which is a constant column with zero values.
  2. levels must be greater than 1, which is a reasonable assumption. If you want to consider levels=1 for the sake of a more generic formula, then you can add an IF condition before REDUCE, because we don't need a recursion for that, for example, IF(level=1, CNTS(1), REDUCE(...)).

Upvotes: 0

Related Questions