• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count empty numbers and cells

deciog

Active Member
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
 

Attachments

Fluff13

Active Member
How about
CSS:
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

Well-Known Member
@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).
Code:
= 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

Excel Ninja
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)&"]"),"")

70305
 

Attachments

Last edited:

shrivallabha

Excel Ninja
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

Active Member
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
 
Last edited:

Peter Bartholomew

Well-Known Member
@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.
70318
It may be possible to follow this idea and further simplify; after all,
isNumber*precededBySpace? is closely related to isSpace*followedByNumber?
 

Attachments

Top