Count empty numbers and cells

[deciog]

In VBA only formulates Excel 2016 version and also for version 365

I need to count sequence of numbers and also sequence of empty cells

Thank you very much in advance

Decio

 

[Fluff13]

How about

Sub deciog()
   Dim Cl As Range
   Dim i As Long
  
   For Each Cl In Range("C2", Range("C" & Rows.Count).End(xlUp))
      With Cl.Resize(, 25).SpecialCells(xlConstants).Areas
         For i = 1 To .Count
            Cl.Offset(, 25 + i).Value = .Item(i).Count
         Next i
      End With
      With Cl.Resize(, 25).SpecialCells(xlBlanks).Areas
         For i = 1 To .Count
            Cl.Offset(, 38 + i).Value = .Item(i).Count
         Next i
      End With
   Next Cl
End Sub

 

[Peter Bartholomew]

@deciog
Good call for the VBA!
Naturally I wanted to solve the problem with a dynamic array but it was not easy! I used the beta release function LET to organise the calculation and a function ACCUMULATE, written by Charles Williams to address the problem of DA not supporting breakup of array formulae. The function is intended to turn cash flows into balances and allows for variable interest rates (I have used -100% to zero an accumulation step).

= LET(
  sign, SIGN(sequence),
  consecutive, ACCUMULATE(sign, 0, sign - 1),
  step, DIFF(consecutive, ,-1) + (k=25)*consecutive,
  FILTER(step, step>0) )

1. The process consists of flagging numbers by 1
2. Accumulate 1s until a 0 is found
3. Compare consecutive numbers to identify the steps
4. Correction to ensure final value is included
5. Filter to give the remaining positive counts

 

[bosco_yip]

Another formula option

1] "Number Sequence", in C8 CSE formula copied across right and down :

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,TEXT(FREQUENCY(IF($C2:$AA2<>"",$C$1:$AA$1),IF($C2:$AA2="",$C$1:$AA$1)),"0;;;"))&"</b></a>","//b["&COLUMN(A1)&"]"),"")

2] "Space Sequence", in C14 CSE formula copied across right and down :

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,TEXT(FREQUENCY(IF($C2:$AA2="",$C$1:$AA$1),IF($C2:$AA2<>"",$C$1:$AA$1)),"0;;;"))&"</b></a>","//b["&COLUMN(A1)&"]"),"")

 

[shrivallabha]

If TEXTJOIN function is available to you then you can try below formula as well.

In cell AC2:
=LEN(TRIM(MID(SUBSTITUTE(" "&TRIM(TEXTJOIN("",TRUE,IF($C2:$AA2<>"",1," ")))," ",REPT(" ",99)),99*COLUMNS($A$1:A$1),99)))
Copy down and across.

In cell AP2:
=LEN(TRIM(MID(SUBSTITUTE(" "&TRIM(TEXTJOIN("",TRUE,IF($C2:$AA2="",1," ")))," ",REPT(" ",99)),99*COLUMNS($A$1:A$1),99)))
Copy down and across.

Both are essentially the same formulas except for the slight change (marked in red). Blank cells will be marked with 0.

 

[deciog]

Fluff13, good morning.

It worked perfectly thank you very much.


Peter, good morning.

In my version Microsoft 365 does not have this function ACCUMULATE and DIFF.


bosco_yip, Good morning.

It worked perfectly thank you very much.


shrivallabha, good morning.

Perfect , it worked very well thanks.

Decio

 

[Fluff13]

You're welcome & thanks for the feedback.

 

[Peter Bartholomew]

@deciog
Yes, I didn't post the workbook because it wouldn't work on other 365 installations. I may choose only to develop for MS365 these days but to rely on Charles Williams's FastExcel is taking it too far.
I struggled to find a dynamic array solution, but then realised that I was accumulating ones (otherwise known as 'counting'!)
The latest attempt filters to find the position of first and last numbers and subtracts.

It may be possible to follow this idea and further simplify; after all,
isNumber*precededBySpace? is closely related to isSpace*followedByNumber?

 

[deciog]

Peter

Ecelente worked perfectly thank you very much

Decio