Count for each 30 day period starting with the first "x", if there are 4 or more x's in it.

[Stephen_IV]

I need a count that will tell me in any 30 day period starting with the first "x" if there are 4 or more x's in it. I have approx 60,000 rows of data. Thanks in advance!!!

8/30/2021	8/31/2021	9/1/2021	9/2/2021	9/3/2021	9/4/2021	9/5/2021	9/6/2021	9/7/2021	9/8/2021	9/9/2021	9/10/2021	9/11/2021	9/12/2021	9/13/2021	9/14/2021	9/15/2021	9/16/2021	9/17/2021	9/18/2021	9/19/2021	9/20/2021	9/21/2021	9/22/2021	9/23/2021	9/24/2021	9/25/2021	9/26/2021	9/27/2021	9/28/2021	9/29/2021	9/30/2021	10/1/2021	10/2/2021	10/3/2021	10/4/2021	10/5/2021	10/6/2021	10/7/2021	10/8/2021	10/9/2021	10/10/2021	10/11/2021	10/12/2021	10/13/2021	10/14/2021	10/15/2021	10/16/2021	10/17/2021	10/18/2021	10/19/2021	10/20/2021	10/21/2021	10/22/2021	10/23/2021	10/24/2021	10/25/2021	10/26/2021	10/27/2021	10/28/2021	10/29/2021	10/30/2021	10/31/2021	11/1/2021	11/2/2021	11/3/2021	11/4/2021	Count
					x																			x																			x	x										x					x								1

8/30/2021	8/31/2021	9/1/2021	9/2/2021	9/3/2021	9/4/2021	9/5/2021	9/6/2021	9/7/2021	9/8/2021	9/9/2021	9/10/2021	9/11/2021	9/12/2021	9/13/2021	9/14/2021	9/15/2021	9/16/2021	9/17/2021	9/18/2021	9/19/2021	9/20/2021	9/21/2021	9/22/2021	9/23/2021	9/24/2021	9/25/2021	9/26/2021	9/27/2021	9/28/2021	9/29/2021	9/30/2021	10/1/2021	10/2/2021	10/3/2021	10/4/2021	10/5/2021	10/6/2021	10/7/2021	10/8/2021	10/9/2021	10/10/2021	10/11/2021	10/12/2021	10/13/2021	10/14/2021	10/15/2021	10/16/2021	10/17/2021	10/18/2021	10/19/2021	10/20/2021	10/21/2021	10/22/2021	10/23/2021	10/24/2021	10/25/2021	10/26/2021	10/27/2021	10/28/2021	10/29/2021	10/30/2021	10/31/2021	11/1/2021	11/2/2021	11/3/2021	11/4/2021	count
	x												x								x								x		x				x		x					x											x					x	x			x				x	2

 

[mumps]

Try:

Sub countx()
    Application.ScreenUpdating = False
    Dim v As Variant, r As Long, c As Long, cnt As Long
    v = Range("A1").CurrentRegion.Value
    For r = 2 To UBound(v)
        For c = 1 To UBound(v, 2)
            If v(r, c) = "x" Then
                If WorksheetFunction.CountA(Cells(r, c).Resize(, 30)) >= 4 Then
                    cnt = cnt + 1
                End If
                Exit For
            End If
        Next c
    Next r
    MsgBox cnt
    Application.ScreenUpdating = True
End Sub

 

[Stephen_IV]

mumps,
First of all thank you so much for responding to my post! It worked for a single line of data but what i need is for 60,000 lines. Please see below.

Count	8/30/2021	8/31/2021	9/1/2021	9/2/2021	9/3/2021	9/4/2021	9/5/2021	9/6/2021	9/7/2021	9/8/2021	9/9/2021	9/10/2021	9/11/2021	9/12/2021	9/13/2021	9/14/2021	9/15/2021	9/16/2021	9/17/2021	9/18/2021	9/19/2021	9/20/2021	9/21/2021	9/22/2021	9/23/2021	9/24/2021	9/25/2021	9/26/2021	9/27/2021	9/28/2021	9/29/2021	9/30/2021	10/1/2021	10/2/2021	10/3/2021	10/4/2021	10/5/2021	10/6/2021	10/7/2021	10/8/2021	10/9/2021	10/10/2021	10/11/2021	10/12/2021	10/13/2021	10/14/2021	10/15/2021	10/16/2021	10/17/2021	10/18/2021	10/19/2021	10/20/2021	10/21/2021	10/22/2021	10/23/2021	10/24/2021	10/25/2021	10/26/2021	10/27/2021	10/28/2021	10/29/2021	10/30/2021	10/31/2021	11/1/2021	11/2/2021	11/3/2021	11/4/2021
2						x						x	x	x	x										x																			x	x										x					x
1	x															x													x				x	x	x	x					x									x			x		x		x
2				x									x								x								x		x				x		x					x											x					x	x			x				x
0	x													x											x									x															x
2									x								x		x					x																			x										x	x	x					x
1																																				x				x								x	x

 

[mumps]

What does the "Count" column represent? Are there any blank rows in your data?

 

[Stephen_IV]

Thanks again mumps! The count column represents how many groups of 30 (starting with the first x) has >=4 x's in it. No blank rows.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Stephen_IV]

Truancy Formula 30 Day Groups.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR
1	Count	8/30/2021	8/31/2021	9/1/2021	9/2/2021	9/3/2021	9/4/2021	9/5/2021	9/6/2021	9/7/2021	9/8/2021	9/9/2021	9/10/2021	9/11/2021	9/12/2021	9/13/2021	9/14/2021	9/15/2021	9/16/2021	9/17/2021	9/18/2021	9/19/2021	9/20/2021	9/21/2021	9/22/2021	9/23/2021	9/24/2021	9/25/2021	9/26/2021	9/27/2021	9/28/2021	9/29/2021	9/30/2021	10/1/2021	10/2/2021	10/3/2021	10/4/2021	10/5/2021	10/6/2021	10/7/2021	10/8/2021	10/9/2021	10/10/2021	10/11/2021	10/12/2021	10/13/2021	10/14/2021	10/15/2021	10/16/2021	10/17/2021	10/18/2021	10/19/2021	10/20/2021	10/21/2021	10/22/2021	10/23/2021	10/24/2021	10/25/2021	10/26/2021	10/27/2021	10/28/2021	10/29/2021	10/30/2021	10/31/2021	11/1/2021	11/2/2021	11/3/2021	11/4/2021	11/5/2021	11/6/2021
2	2						x						x	x	x	x										x																			x	x										x					x
3	1	x															x													x				x	x	x	x					x									x			x		x		x
4	2				x									x								x								x		x				x		x					x											x					x	x			x				x
5	0	x													x											x									x															x
6	2									x								x		x					x																			x										x	x	x					x
7	1																																				x				x								x	x
Sheet3 (2)

 

[mumps]

Try:

Sub countx()
    Application.ScreenUpdating = False
    Dim v As Variant, r As Long, c As Long, cnt As Long, LastRow As Long, lCol As Long, fnd As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    For r = 2 To LastRow
        Set fnd = Rows(r).Find("x", LookIn:=xlValues, LookAt:=xlWhole)
        If Not fnd Is Nothing Then
            For c = fnd.Column To lCol Step 30
                If WorksheetFunction.CountA(Cells(r, c).Resize(, 30)) >= 4 Then
                    cnt = cnt + 1
                End If
            Next c
            Range("A" & r) = cnt
        End If
        cnt = 0
    Next r
    Application.ScreenUpdating = True
End Sub

 

[mumps]

A minor change:

Sub countx()
    Application.ScreenUpdating = False
    Dim r As Long, c As Long, cnt As Long, LastRow As Long, lCol As Long, fnd As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    For r = 2 To LastRow
        Set fnd = Rows(r).Find("x", LookIn:=xlValues, LookAt:=xlWhole)
        If Not fnd Is Nothing Then
            For c = fnd.Column To lCol Step 30
                If WorksheetFunction.CountA(Cells(r, c).Resize(, 30)) >= 4 Then
                    cnt = cnt + 1
                End If
            Next c
            Range("A" & r) = cnt
        End If
        cnt = 0
    Next r
    Application.ScreenUpdating = True
End Sub

 

[Stephen_IV]

mumps, thank you so much! That did the trick. I greatly appreciate all of your help and patience!