How to get the how many times this come in this sheet

Vishaal

Well-known Member
Joined
Mar 16, 2019
Messages
530
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Thanks in advance,

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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
with PowerQuery

S.no.MarksYes/noCRCount
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:
[SIZE=1]// 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[/SIZE]

extended example:

S.no.MarksYes/noCRCount
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
 
Last edited:
Upvote 0
Results start "D1".
Code:
[COLOR="Navy"]Sub[/COLOR] MG19May12
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] b [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range, oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Dn.Value = "yes" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn

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

[D1] = """Yes"" 2 Times": [E1] = c
[D2] = """Yes"" 3 Times": [E2] = b
[D3] = "Max  ""Yes"" Times": [e3] = oMax
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
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:
[table="width: 500"]
[tr]
	[td]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[/td]
[/tr]
[/table]
 
Upvote 0
Hi Mick, Showing the following error

Run-time error '91':

Object variable or with block variable not set

help pls
 
Upvote 0
help pls bro................waiting for reply
 
Upvote 0
i have tried it through vba but not working, help pls
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top