Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

Count "x" number in a row

Hi Experts

I am looking for a solution for something that seems very simple on the surface but I am not sure if it really is in reality.  

Here's the challenge:
              A      B      C      D      E      F      G      H     I      J     K    L    M   N
Row:      1      1      0      0       1      0      0       0     1    1     0    0    0    1  

I am looking to ONLY count the zero's in the above example when there is 3 in a row, ignoring all others values as well as any zero that isn't 3 in a row.  

Any help would be greatly appreciated as always.  

spudmcc
0
spudmcc
Asked:
spudmcc
  • 12
  • 9
  • 7
  • +3
2 Solutions
 
Saqib Husain, SyedEngineerCommented:
What is the answer for the above example?
0
 
Martin LissRetired ProgrammerCommented:
So I guess the answer is 6.
0
 
spudmccAuthor Commented:
Yes, it would be six.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
redmondbCommented:
Hi, spudmcc,
=14-LEN(SUBSTITUTE(SUBSTITUTE(CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1,M1,N1),"000","###"),"#",""))

Open in new window

The above assumes that, for example, four zeros in a row only count as three (because the the three zeros have been "used up" before the fourth one comes along). So for your example, it gives six, but it also gives six for the following...

    1      1      0      0       1      0      0       0     0    1     0    0    0    1  

Is this correct?

Regards,
Brian.
0
 
spudmccAuthor Commented:
The amount of items in each row could vary.  It isn't always in columns A-J.  
Also, I should clarify that it should count anything >=3.  So if we have

             A        B        C         D         E         F            G        H      I         J         K         L        M      
Row1    1        0         0         0         0         1           0          0     0        1        0          0         0

Row 2   1        1         0          0        1          1           1          1      1       0        0          0        0

Row 1 would equal 10
Row 2 would equal 4

Sorry for not being clear the first time.
0
 
redmondbCommented:
spudmcc,

Thanks, but we need some more, please!
(1) What is the maximum no. of columns?.
(2) Can we shift the columns to the right, so that the formula is in column A?
(3) When a column isn't being used, what will be in it? (It would be ideal if you could give us a sample file!)

Thanks,
Brian.
0
 
redmondbCommented:
spudmcc,

Pending answers to the above, please see the attached. A few points...
(1) It handles the first 14 or fewer columns. It can be easily changed to handle more columns.
(2) The cells in column O must have a "1" in them. (Sorry, I'll try to get rid of this.)
(3) The formulas in column P must be array-entered (CTRL-SHIFT-ENTER). Let me know if you need more on this.
(4) I manually checked the results, as best I could. I'm hoping you have some test data as a definitive test!

The formula in P1 is
{=SUM(IF((A1:L1&""="0")*(B1:M1&""="0")*(C1:N1&""="0"),3-IF(D1:O1&""="0",2,0)))}

Open in new window

Regards,
Brian.
Three-in-a-Row.xls
0
 
redmondbCommented:
Oh, I've assumed that this must be a formula. Is that correct? (Of course a macro would be pretty simple!)
0
 
redmondbCommented:
... and here is that macro. It ignores column O and outputs to column Q...
Option Explicit

Sub Three_in_a_Row()
Dim xLast_Row As Long
Dim i As Long
Dim j As Long
Dim xZeros As Long
Dim xCount As Long

Sheets("Sheet1").Activate

xLast_Row = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row

For i = 1 To xLast_Row
    xCount = 0
    xZeros = 0
    For j = 1 To 14
        If Cells(i, j) = "0" Then
            xZeros = xZeros + 1
            If xZeros = 3 Then
                xCount = xCount + 3
            ElseIf xZeros > 3 Then
                xCount = xCount + 1
            End If
        Else
            xZeros = 0
        End If
    Next
    Cells(i, 17) = xCount
Next

End Sub

Open in new window

0
 
byundtCommented:
Brian,
You did better than you thought. I'm not seeing any need for column O to have a 1, having used Paste Special...Values to compare results with either ones or blanks in column O.

Brad
0
 
redmondbCommented:
Brad,

Oops, I should have said "non-zero" rather than "1". (The "1" was a hang-over from an earlier version.)

The problem is that I need an array of D1:N1's values - but it needs to have 12 entries not 11, hence column O. Any idea how I could do this without the extra column?

Regards,
Brian.
0
 
Rory ArchibaldCommented:
=SUMPRODUCT((FREQUENCY(IF(A1:N1=0, COLUMN(A1:N1)), IF(A1:N1<>0, COLUMN(A1:N1)))>=3)*(FREQUENCY(IF(A1:N1=0, COLUMN(A1:N1)), IF(A1:N1<>0, COLUMN(A1:N1)))))
array-entered should work. Can be extended for different criteria than 3 quite easily. Barry can probably do it in 10 characters or fewer...
0
 
redmondbCommented:
Rory,

Nice one! Small change (to handle less than 14 entries as requested by OP - see my last two examples)...
{=SUMPRODUCT((FREQUENCY(IF(A1:N1&""="0", COLUMN(A1:N1)), IF(A1:N1&""<>"0", COLUMN(A1:N1)))>=3)*(FREQUENCY(IF(A1:N1&""="0", COLUMN(A1:N1)), IF(A1:N1&""<>"0", COLUMN(A1:N1)))))}

Open in new window

Regards,
Brian.
0
 
Rory ArchibaldCommented:
Ah, my understanding was that there were always values in the cells, but the number of columns might alter, in which case simply changing the ranges is all that's necessary. For blank cells, yes, we need an adjustment like that. :)
0
 
redmondbCommented:
Rory,

"my understanding was that there were always values in the cells"
Yes, you could be right. The exact format is unclear and we're waiting for a response from the OP.

Regards,
Brian.
0
 
redmondbCommented:
Trivial point, Rory - can't "SumProduct" be replaced by "Sum"?
0
 
Rory ArchibaldCommented:
For that syntax, yep.
0
 
spudmccAuthor Commented:
Yes, the number of columns could vary but each cell will always contain a "1" or a "0".  

Andy (Spudmcc)
0
 
redmondbCommented:
Andy,

Thanks. So how should the formula recognise the number of columns?

Regards,
Brian.
0
 
Rory ArchibaldCommented:
Do you mean a different number of columns in use for each row, or can you simply adjust the ranges in the formulas provided earlier to match your data?
0
 
spudmccAuthor Commented:
Each row will have the same number of columns but workbook to workbook the number of columns may change.  Todays workbook may have 20 columns but tomorrows might have 25.  We only have to be concerned with 1 day at a time and never need to merge books but the solution need to be flexible enough that it can be used with different column numbers.  

I hope that makes some sort of sense.  

A
0
 
Rory ArchibaldCommented:
Well, I'm not sure what you mean by flexible - all you have to do with the formulas is change the ranges to point at the columns you want to check. So if you need to check cols B:X change the formula from

=SUMPRODUCT((FREQUENCY(IF(A1:N1=0, COLUMN(A1:N1)), IF(A1:N1<>0, COLUMN(A1:N1)))>=3)*(FREQUENCY(IF(A1:N1=0, COLUMN(A1:N1)), IF(A1:N1<>0, COLUMN(A1:N1)))))

to

=SUMPRODUCT((FREQUENCY(IF(B1:X1=0, COLUMN(B1:X1)), IF(B1:X1<>0, COLUMN(B1:X1)))>=3)*(FREQUENCY(IF(B1:X1=0, COLUMN(B1:X1)), IF(B1:X1<>0, COLUMN(B1:X1)))))
0
 
spudmccAuthor Commented:
This works great!  I assumed I would have to change the range and that isn't any problem at all.  Thanks for all of your hard work, time and patience.  

Andy (Spudmcc)
0
 
Rory ArchibaldCommented:
I think a points split would be more appropriate since Brian gave you several options for solutions?
0
 
spudmccAuthor Commented:
You are absolutely correct.  Could I please ask the moderator to split the points between rorya and redmondb.  

Thanks Rory!

Andy
0
 
Rory ArchibaldCommented:
I've requested mod attention to get this reopened and you can then re-allocate as you wish. :)
0
 
redmondbCommented:
Folks,

I appreciate the effort, but really don't worry about it! Rory's excellent solution did the job - and I learned a lot from it. Win:win!

Regards,
Brian.
0
 
Rory ArchibaldCommented:
Thanks, Brad.
0
 
spudmccAuthor Commented:
Thanks Experts for the help!  

Andy
0
 
Rory ArchibaldCommented:
you're welcome. :)
0
 
redmondbCommented:
Thanks all!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 12
  • 9
  • 7
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now