# Thread: How to get the how many times this come in this sheet Thanks: 0 Likes: 0

1. ## How to get the how many times this come in this sheet

I have following sheet
S.no. Marks Yes/no
1. 13. No
2. 16. yes
3. 18. yes
4. 19. No
5. 22 yes
6. 24. yes
7. 26. yes
8. 33. No
9. 35. No
10. 12. yes
11. 17. No
12. 38. yes
13. 40. yes
14. 42. yes
15. 44. yes
16. 46. yes

My query is I want to know how many times

1. 'continuesly three time yes' in third column - come in the above sheet

2. 'continuesly two time yes' in third column - come in the above sheet

3. 'Maximum time continues yes' in third column - comes in the above sheet

And is
1. Three time yes comes only one time
2. Two time yes comes only one time
3. Maximum time continues yes comes is 5

Please provide the VBA code or any formula for the above three point

2. ## Re: How to get the how many times this come in this sheet

with PowerQuery

 S.no. Marks Yes/no CR Count 1 13 No 1 1 2 16 Yes 2 1 3 18 Yes 3 1 4 19 No 5 1 5 22 Yes 6 24 Yes 7 26 Yes 8 33 No 9 35 No 10 12 Yes 11 17 No 12 38 Yes 13 40 Yes 14 42 Yes 15 44 Yes 16 46 Yes

Code:
```// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Grp1 = Table.Group(Source, {"Yes/no"}, {{"Count", each _, type table}, {"CR", each Table.RowCount(_), type number}},GroupKind.Local),
Filter = Table.SelectRows(Grp1, each ([#"Yes/no"] = "Yes")),
Grp2 = Table.Group(Filter, {"CR"}, {{"Count", each Table.RowCount(_), type number}}),
Sort = Table.Sort(Grp2,{{"CR", Order.Ascending}})
in
Sort```
extended example:

 S.no. Marks Yes/no CR Count 1 13 No 1 1 2 16 Yes 2 2 3 18 Yes 3 1 4 19 No 5 1 5 22 Yes 6 24 Yes 7 26 Yes 8 33 No 9 35 No 10 12 Yes 11 17 No 12 38 Yes 13 40 Yes 14 42 Yes 15 44 Yes 16 46 Yes 17 47 No 18 48 Yes 19 49 Yes

3. ## Re: How to get the how many times this come in this sheet

Results start "D1".
Code:
```Sub MG19May12
Dim Rng As Range, Dn As Range, c As Long, b As Long, nRng As Range, oMax As Long
Set Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
For Each Dn In Rng
If Dn.Value = "yes" Then
If nRng Is Nothing Then Set nRng = Dn Else Set nRng = Union(nRng, Dn)
End If
Next Dn

For Each Dn In nRng.Areas
If Dn.Count = 2 Then c = c + 1
If Dn.Count = 3 Then b = b + 1
oMax = Application.Max(oMax, Dn.Count)
Next Dn

[D1] = """Yes"" 2 Times": [E1] = c
[D2] = """Yes"" 3 Times": [E2] = b
[D3] = "Max  ""Yes"" Times": [e3] = oMax
End Sub```
Regards Mick

4. ## Re: How to get the how many times this come in this sheet

Here is a function (can be called from other VBA code or can be used directly within an Excel formula) that takes one required argument (the vertical range of cells containing the Yes/No values) and one optional argument (if you specify a positive number, it will return the count of that many consecutive yesses within the range and, if omitted or if a zero is passed into the second argument, it will return the maximum count for consecutive yesses within the range)...
Code:
```Function Yesses(VertRng As Range, Optional Count As Long) As Long
Dim Combined As String, V As Variant
Combined = "NO" & Replace(UCase(Join(Application.Transpose(VertRng), "")), " ", "") & "NO"
If Count Then
Yesses = UBound(Split(Combined, "NO" & Application.Rept("YES", Count) & "NO"))
Else
For Each V In Split(Application.Trim(Replace(Combined, "NO", " ")))
If Len(V) / 3 > Yesses Then Yesses = Len(V) / 3
Next
End If
End Function

```

thanks bro,

Thanks bro.

7. ## Re: How to get the how many times this come in this sheet

You are welcome

Have a nice day

8. ## Re: How to get the how many times this come in this sheet

Hi Mick, Showing the following error

Run-time error '91':

Object variable or with block variable not set

help pls

10. ## Re: How to get the how many times this come in this sheet

i have tried it through vba but not working, help pls